Solved: Matrix type vlookup

[John_Mc]

Hi All,

The end stages of creating some reports...be glad when it's over..but...

I am trying to sum values based upon two criteria.

I've attached an example with comments to make life easier.

I put raw data into the raw sheet (a data dowload of all the cost centres and accounts codes). I then run a macro to add up each instance of the account code for each cost centre. It pastes the data into the "Extracted" sheet.

From here I would like to be able to look up the end value. So on the "Sums" sheet, for cost centre 2001, I want to lookup up the values for all the agency staff employed. Agency staff account codes are 1180, 1181, 1182, 1183, 1184, 1184, 1395.

So add up all the values in these account codes (only for the relevant cost centre) and put it in one value on the sums tab.

Any ideas on if there is a specific function to do this (related to vlookup maybe?)



Cheers,
John Mc

[Shazam]

Hope this helps!


Input formula in cell B12 and copy down.


=SUMPRODUCT((Extracted!$A$3:$A$216=A12)*(ISNUMBER(MATCH(Extracted!$C$2:$CP$ 2,$B$3:$B$9,0))*(Extracted!$C$3:$CP$216)))

[OBP]

Sorry I misread the Comment in B12

[OBP]

Hope this helps!


Input formula in cell B12 and copy down.


=SUMPRODUCT((Extracted!$A$3:$A$216=A12)*(ISNUMBER(MATCH(Extracted!$C$2:$CP$ 2,$B$3:$B$9,0))*(Extracted!$C$3:$CP$216)))

Am I missing something?
I can only see 2 columns that meet the criteria of 1180 1181 1182 1183 1184 1185 1395 and they are 1182 and 1395, but they only total 10 for cost or 44 for hours for Cost Centre 2001.
The formula above gives a total of 98 for cost.

[Shazam]

Am I missing something?
I can only see 2 columns that meet the criteria of 1180 1181 1182 1183 1184 1185 1395 and they are 1182 and 1395, but they only total 10 for cost or 44 for hours for Cost Centre 2001.
The formula above gives a total of 98 for cost.

I think he wants to extract the data from the worksheet "extracted"?

[OBP]

That is the worksheet I am looking at.

[Shazam]

Ok now I know what I was missing.

Input formula in cell B12 and copy down.


=SUMPRODUCT((Extracted!$A$3:$A$216=A12)*(ISNUMBER(MATCH(Extracted!$C$2:$CP$ 2,$B$3:$B$9,0))*(Extracted!$B$3:$B$216="Description")*(Extracted!$C$3:$CP$2 16)))


Nice catch OBP

[John_Mc]

Hi Shazam,

Thanks for your help on this. The time difference and a weekend explain my slightly delayed response.

I've tried to copy the formula down, but it seems to sum to zero and not the results i was expecting. Any ideas on why this is happening? I've looked up the help files for each function, but i'm still quite confused.

Cheers,
John

[OBP]

John, can you give an example of the a couple of the values that you are expecting please?

[John_Mc]

Hi OBP

I was just editing the example file to illustrate what i meant, when i noticed that i needed to change the description titile.

Only the first line in the example file has the term "Description" which is why the first one worked and the subsequent ones didn't when i copied it down.

I should have seen it yesterday, but i think it was one of those typical Excel moments when you don't see it as soon as you should (and we've all had those!).

Thanks to both you and Shazam for all your help

Cheers,
John