Sumif with choice

Dear Experts-exchange,

on the enclosed file, you'll find two sheets, one called "Forecast", and the other's "Actual".
I need a formula where, if the number on the right cornet of the "Forecast" sheet is one, the
"Actual Forecast" has to be the sum of all monthly forecast on the same sheets. IF the number is 2, meaning February, then the "Actual Forecast" has to be the sum of the sheet "Actual", month January, plus the forecast of the remaining months on the "Forecast" sheet. When the number will be 3, then "Actual Forecast" = sum of month January + February presents on the "Actual" sheet, plus the remaining months on the "Forecast sheet"..and so on.

If you didn't understand something, feel free to ask me more specifications.

Thank you in advance for your support and regards,
Danilo
test.xls
systel_cicorAsked:
Who is Participating?
 
Rob HensonFinance AnalystCommented:
Attached with amendments as per original comment above. If you want to look at the shorter option, let me know.

Cheers
Rob H
sumif-test2.xls
0
 
suvmitraCommented:
This can be done easily with VBA. Do you need a macro instead of a formula? Let me know.
0
 
ghoshsaikat83Commented:
Please find the attached file...

Joydip
test-1-.xls
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Rob HensonFinance AnalystCommented:
Danilo,

See attached. I assume that even though you only mention months 1 & 2 in your example you want it to accommodate all 12.

I have added a row to the top of the actual sheet to number the months. This also assumes that the customers are in the same order and row on actual and forecast. This could be got round of so required.

See columns M & N on Forecast sheet for sumif formulae, column B is then just a sum of the two. They could be combined in column B if so required.

Hope this helps.
Rob H
sumif-test.xls
0
 
systel_cicorAuthor Commented:
Dear All,

I did found problems on adapt the formula on the real file. Therefore I've decided to put the real file, without the real names and numbers, because unfortunately I don't have a lot of time to adapt the formula, in order to works correcty..




test2.xls
0
 
Rob HensonFinance AnalystCommented:
See formula in column F of sheet 'Actual 2010'.

I have created a table of forecast data over to the right of the main data table, linked to the Expected column for each month and then refer to this in the sumif.

Hope this helps.

Cheers
Rob H
sumif-test2.xls
0
 
Rob HensonFinance AnalystCommented:
NB - Major assumption that the customers are in the same order on each sheet. If not we may have to adopt a different solution.

RH
0
 
systel_cicorAuthor Commented:
Dear Mr. Robhenson,

sorry it was my mistake, the formula which should be corrected/changed, is already present on the "Forecast 2010" Sheet, on the cell "G3". It changes, but it doesn't take the actual when I change numbers.
Danilo
0
 
Rob HensonFinance AnalystCommented:
Danilo,

The formula in G3 does not make sense at all, unless that is what is wrong of course.

It says if the value of A1 (assumed month indicator) is less than 2 then take value from K3 (forecast for January), otherwise look at column A of actual sheet (contains merged cells saying "Key Customers" and "Customers"), compare with contents of B2 (Heading?) and addup column H of Actual sheet.

Errors:
Column B of forecast contains customer references which do not appear in column A of actual, therefore should be comparing column B on both sheets.

Formula in G3 refers to customer reference in B2, I think this should be B3.

Hope that helps
Cheers
Rob H
0
 
systel_cicorAuthor Commented:
Dear all

the formula has to be inserted in the column G of the "Forecast 2010" and, as explained in the comment,
it should work so:

when month is = 1 then the result has to be the sum of JAN 10 TOT + FEB 10 TOT .. till DEC 10 TOT which are in the sheet "Forecast 2010".
when month is = 2 then the result has to be the sum of Jan Sheet "Actual 2010" + sum of (FEB 10 T +
MAR 10 T + ... till DEC 10 T) of sheet "Forecast 2010".
when month is = 3 result has to be sum of Jan + Feb Sheet "Actual 2010 + sum of ( MAR 10 + APR 10 ...till DEC 10) of sheet " Forecast 2010"
and so on..

I add again the file without formula.
Regards,
Danilo
sumif-test3.xls
0
 
Rob HensonFinance AnalystCommented:
The formula you had before was fine except for the amendments I pointed out.

Admittedly, there would no doubt be a shorter option. Add a numerical labelling to each month and then use an IF statement comparing the month number with the reference number to label the tops of the columns required with actual or blank on the actual sheet; forecast or blank on the forecast sheet and then use a SUMIF formula to addup the actual sheet plus another SUMIF formula to add up the forecast sheet:

=SUMIF(ActualLabelRow,"Actual",ActualDataRow)+SUMIF(ForecastLabelRow,"Forecast",ForecastDataRow)

It doesn't matter on the forecast if you include the complete range, so long as only the column with the data you want included has the Forecast label.

Cheers
Rob H
0
 
systel_cicorAuthor Commented:
Thank you Mr Robbhenson,

the formula as well as the description were perfect!
Again thank you for the good job.
Seen the day..wish you also Merry Christmas,
Danilo
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.