Need help with this sum product formula......please.

[TonyTonyTony]

I stumbled upon this site and had to sign up. Great members and topics.


I have 31 (days of the month) pages and one Summary page. The following formula works just fine for one single page.

=sumproduct(sumif(Indirect(''''''&1&"'!B4:B13),"DEANS",Indirect(""'&1&"'!H4:H13")))


What I've been having to do is add + and repeat the same formula another 30 times, inserting the correct page #. This is extremely time consuming and I know there is a better way. Can anyone lend a hand?

 

[dan_l]

A sample workbook may help.....

 

[dan_l]

I should add: the indirect formula is pretty robust. So if your cells look like this:


Sheet1!A:A

Sheet2!A:A


it's totally reasonable to do sum(indirect(a1)) and sum(indirect(a2)) which would result in the sum of column A on sheet 1 and the sum of column B on sheet2.

 

[Kyle McGhee]

Hi TonyTonyTony,


Modify your existing formula to this (assuming your sheet names are 1,2,3,4...31):


=SUMPRODUCT(SUMIF(INDIRECT(ROW($A$1:$A$31)&"!B4:B13"),"DEANS",INDIRECT(row($A$1:$A$31)&"!H4:H13")))

 

[TonyTonyTony]

I'll try it at work tomorrow. If that works you will have saved me about 14 hours of work. Thanks.

 

[TonyTonyTony]

Kyle, that formula works perfect. I finished my summary spread sheet this morning. 14 hours down to about 30 min. Thank you.