Solved

# Sumif with choice

Posted on 2009-12-21
228 Views
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.

Danilo
test.xls
0
Question by:systel_cicor

LVL 9

Expert Comment

This can be done easily with VBA. Do you need a macro instead of a formula? Let me know.
0

LVL 5

Expert Comment

Joydip
test-1-.xls
0

LVL 31

Expert Comment

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

Author Comment

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

LVL 31

Expert Comment

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

LVL 31

Expert Comment

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

Author Comment

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

LVL 31

Expert Comment

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

Author Comment

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

LVL 31

Expert Comment

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

LVL 31

Accepted Solution

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

Author Closing Comment

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

## Featured Post

### Suggested Solutions

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.