SUMPRODUCT for multiple sheets

[nikkis]

Hi,

I have created a sumproduct that works for a single sheet:
=SUMPRODUCT(--(A4:A30="Clouds EASY"), C4:C30)

However, I have 40 plus sheets that I need to apply this function to (and over 200 effects). Currently the formula for three sheets (760, 236 and 980) is:

=SUM(SUMPRODUCT(--('760'!A4:A30="Clouds EASY"), '760'!C4:C30), SUMPRODUCT(--('980'!A4:A30="Clouds EASY"), '980'!C4:C30),SUMPRODUCT(--('236'!A4:A30="Clouds EASY"), '236'!C4:C30))

This works fine but is incredibly time consuming, especially since I have to do this for EVERY sheet and every effect in my document. Any recommendations on a solution?

Thanks!!

Nikki

 

[Haseeb Avarakkan]

Hello Nikki,

If you just have one condition, use SUMIF instead of SUMPRODUCT.

In your all sheets, let's say in E1 enter,

=SUMIF(A4:A30,"Clouds EASY",C4:C30)

Then you can simply use SUM like,

=SUM('FirstSheet:LastSheet'!E1)

Or,

Enter your all sheet names in a range, let's say E2:E40, then try

=SUMPRODUCT(SUMIF(INDIRECT("'"&E2:E40&"'!A4:A30"),"Clouds EASY",INDIRECT("'"&E2:E40&"'!C4:C30")))

 

[nikkis]

Thank you for your reply Haseeb!

Could you explain the range a little bit further for me? Right now each sheet is named after a scene number in the movie. The three sheets I am practicing on are: 760, 236, 980 (in that order). So the range would be 760:980.

When I entered:

=SUMPRODUCT(SUMIF(INDIRECT("'"&760:980&"'!A4:A30"),"Clouds EASY",INDIRECT("'"&760:980&"'!C4:C30")))

#Ref error appeared. Any idea what I'm doing wrong?

 

[Haseeb Avarakkan]

Enter sheet names in,

E2, 760
E3, 236
E4, 980

Then,

=SUMPRODUCT(SUMIF(INDIRECT("'"&E2:E4&"'!A4:A30"),"Clouds EASY",INDIRECT("'"&E2:E4&"'!C4:C30")))

 

[nikkis]

IT WORKS!!! Thank you sooooo much!!! I have literally spent the last two days trying to figure this thing out!!

 

[Peterfc2]

6 years on and I've just used it too

 

[aquabit]

Whatif I have more than one criteria. I am using sumifs but it is considering only first sheet to add the value