SUMIF - VLOOKUP Combination

**Fraser** · 10-27-2009, 07:29 AM

Hi,

I am sitting at work trying to build myself a spread sheet. I have my raw data, which I am trying to put into a cash flow template I am trying to automate.

For income below are some of the categories:
Week Number 1 2 3 4 5 6
Rent 50,000
Service Charge 10,000
Events
Exhibitions 20,000
Development
Other

I can line up all the totals for the categories by using SUMIF.
e.g: =SUMIF('Year Data'!$U$2:$U$5212,C14,'Year Data'!$O$2:$O$5212)

I then need to split it out over which week it has accured in ans using this forumla can get this:=VLOOKUP(F8,'Year Data'!S2:V5212,4,FALSE)

Week number12345
Rent10,000
Service Charge 10,000
Events
Exhibitions 20,000

What I want is to combine the two SUMIF / VLOOKUP and get it looking like this?
(Like this if possible)
Week number 1 2 3 4 5 6
Rent 10,000 5,000 20,000 15,000
Service Charge 2,5000 2,5000 2,5000 2,5000
Events
Exhibitions 10,000 10,000
Any help will be much appreciated.

Cheers
Fraser

**NBVC** · 10-27-2009, 07:42 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Fraser** · 10-27-2009, 08:29 AM

Please see the spread sheet attached - any help much appreciated.
Cheers
Fraser

**NBVC** · 10-27-2009, 09:09 AM

I am not sure I understand.

Is the results you show the intended results?

Because for week 16 on Rent, it looks like the answer should be 645 not -1000.

Please let me know what the intended results in the columns are to be.

**Fraser** · 10-27-2009, 11:05 AM

NBVC - Thank you for your patients!

Your help is greatly appreciated.

OK updated spread sheet is attached.

Requirements:
Currently trying to set up weekly cash flow spread sheet
I download info from SAGE - "year data", and need to allocate info to this spread sheet.
I need to put the actuals in the correct line e.g rent, and under the correct week.
Please note - the "year data" has been simplified to one transaction per week, per category, when there will be actually 100's of transaction per week for any one category.

If I can get this solved - it would be amazing.

I then want to pull the actuals through to the summary page. the summary page will be actuals up to the week in box C7, and then forecast as defined on the forecast tab after that. Would you use a Hlookup for this?

many Thanks
Fraser

**Igorsp7** · 10-27-2009, 12:21 PM

Please find attached your spreadsheet with the right formulae (see on the light grey background). Note that this is an array formula, so need to press Ctrl + Shift + Enter when entering it.

**NBVC** · 10-27-2009, 12:33 PM

In E13:

=SUMPRODUCT(--('Year Data'!$F$2:$F$519=$C13),--('Year Data'!$D$2:$D$519=E$7),'Year Data'!$C$2:$C$519)

copied down and across

**Fraser** · 10-27-2009, 12:34 PM

Igorsp7 - Thanks a million.

Will be my bed time reading to get it completed tonight.

It is great to find such great help and support.

Cheers
Fraser