?
Solved

Sumif with choice

Posted on 2009-12-21
12
Medium Priority
?
233 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:systel_cicor
12 Comments
 
LVL 9

Expert Comment

by:suvmitra
ID: 26094678
This can be done easily with VBA. Do you need a macro instead of a formula? Let me know.
0
 
LVL 5

Expert Comment

by:ghoshsaikat83
ID: 26094690
Please find the attached file...

Joydip
test-1-.xls
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 26104029
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:systel_cicor
ID: 26104496
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 34

Expert Comment

by:Rob Henson
ID: 26105884
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 34

Expert Comment

by:Rob Henson
ID: 26105904
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

by:systel_cicor
ID: 26106220
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 34

Expert Comment

by:Rob Henson
ID: 26108813
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

by:systel_cicor
ID: 26110877
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 34

Expert Comment

by:Rob Henson
ID: 26112033
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 34

Accepted Solution

by:
Rob Henson earned 800 total points
ID: 26112090
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

by:systel_cicor
ID: 31668456
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question