Solved

Posted on 2009-12-21

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

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

12 Comments

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

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

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

RH

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

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

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

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,"Act

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

Cheers

Rob H

sumif-test2.xls

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

find phone numbers in excel | 6 | 24 | |

executing macros from all files in a folder | 5 | 23 | |

How to add prompts to user defined function? | 6 | 26 | |

Tricky lookup and concatenate formula | 6 | 28 |

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

Connect with top rated Experts

**18** Experts available now in Live!