Sumproduct(sumif(indirect

Hello,

I'm having issues using the SUMPRODUCT function in Excel.

I am attempting to creat a summary sheet to calcualte total spends for different parts of the business.

In this example i have my summary sheet with the business codes in Column A. In sheets 2 & 3 i have the spends against each code. Each code can apeear multiple time on both sheets. See attached

On the summary page i have created a range for my tab names 'SheetList02' and am trying to use the following formula in cell D1...but unsucessfully

=SUMPRODUCT(SUMIF(INDIRECT("'"SheetList02"'!A1:B7"),A9,INDIRECT("'"SheetList02"'!B1:B7")

Can someone help?

Many thanks

Originally Posted by ChemistB

In your example in D9, try

=SUMPRODUCT(--(Sheet2!$A$2:$A$5=$A9)*INDEX(Sheet2!$B$2:$C$5,,MATCH(TEXT(D$7,"mmm"),Sheet2!$B$1:$C$1,0))) + SUMPRODUCT(--(Sheet3!$A$2:$A$5=$A9)*INDEX(Sheet3!$B$2:$C$5,,MATCH(TEXT(D$7,"mmm"),Sheet3!$B$1:$C$1,0)))

That can be dragged across and down. Does that work for you?

Thanks, i tried it on the example and it work great. However i have tried to adapt it to my actual data and can't get it to work. I have pasted the formula into cell D69 and changed it to look at teh required cells etc. can you see where i am going wrong - see attached

Many thanks

Originally Posted by DonkeyOte

If you are not working 3D then use stand alone SUMIFs rather than SUMPRODUCT

Please Login or Register  to view this content.

Note re: original errors:

a) your date headers are now dates on all sheets so MATCH is revised

b) your 2nd SUMPRODUCT referenced 3:11 and 4:11

Thanks, this is all great. Works fine for my 1st 2 sheets but when i add more i just get a load of hashes. Not all sheets have got data in them yet but this shouldn't matter...correct?

To add my additional sheets into the formula i assume that i can just copy the SUMIF and change the sheet names?

Again, please see attached...