sumif on multiple pages?---not just one page

[jeremy via OfficeKB.com]

Here's a function that summarize cells on one page--but I don't know how to
make it summarize on multiple pages....

=SUMIF('1'!$C$15:$C$71,"MC*",'1'!$H$15:$H$71)*2.20462

Any help?

Jeremy

 

[Guest]

Please use sumproduct formula which will be :

=(sumproduct((Page1(rangeA="MC)*(rangeB))*2.20462)+(sumproduct((Page2(rangeA="MC)*(rangeB))*2.20462)+(sumproduct((Page3(rangeA="MC)*(rangeB))*2.20462)

Where rangeA = $C$15:$C$71
and rangeB = $H$15:$H$71

Thanks,

Manish Bajpai

 

[jeremy via OfficeKB.com]

I'm just interested in summing the cells that contain "MC" in the range i
defined, but wasn't sure how to do more than one page....

Is sumproduct the function for this???

thanks
jeremy

 

[Domenic]

Try...

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT(A1&":"&B1))&"'!C15:C71"),"MC*
",INDIRECT("'"&ROW(INDIRECT(A1&":"&B1))&"'!H15:H71"))*2)

....where A1 contains your number for your first sheet/page, such as 1,
and B1 contains your number for your last sheet/page, such as 12.

Hope this helps!

 

[Harlan Grove]

Please use sumproduct formula which will be :

=(sumproduct((Page1(rangeA="MC)*(rangeB))*2.20462)
+(sumproduct((Page2(rangeA="MC)*(rangeB))*2.20462)
+(sumproduct((Page3(rangeA="MC)*(rangeB))*2.20462)

....

What the heck is Page1(..), Page2(..) and Page3(..)? Answer: syntactically,
Excel would treat them as function calls. Since they're not built-in
functions, Excel would attempt to resolve them as udfs. However, you failed
to include the necessary code for these udfs (or mention that they'd need to
be udfs), so your wonderful formula would return a #NAME? error.

Do you know what you're doing?