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

  • Thread starter jeremy via OfficeKB.com
  • Start date
J

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
 
M

Manish Bajpai

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
 
J

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
 
D

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!
 
H

Harlan Grove

Manish Bajpai said:
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)
Click to expand...
....

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?
 
Top