Calculate average in multiple sheets with multiple conditions - Sumproduct calculations take too long

[Shanaya2009]

Hello,

I need help with an alternative formula instead of SUMPRODUCT.

I have over 30 sheets (for every co-worker) with a lot of data. In every sheet column A has dates (from 1st to 31th of the month, each date has multiple entries) and column H has values in %.
I have another sheet (Total) that calculates averages per day for all co-workers and monthly average for all co-workers.
To get the right result, I need to calculate average from all % in column H that have the same date in all sheets.

I have SUMPRODUCT formulas, combined with INDIRECT, but every time I change something anywhere, those formulas calculate and this takes sooooo long.
I have tried with marcos Worksheet.Activate + manual calculation and then in every sheet activesheet.calculate to prevent long calculations, but it still takes too long, and it also disables copy/paste, so this is not an option.

Does anyone have a solution to replace my formula with something else?
I need to calculate the average of all data in column H in every sheet, if column A in every sheet equals to A3 in Total (same date), if column B in every sheet doesn't have the value "x" and if column C in every sheet is not empty.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SHEETS&"'!H:H");INDIRECT("'"&SHEETS&"'!A:A");A3;INDIRECT("'"&SHEETS&"'!B:B");"<>x";INDIRECT("'"&SHEETS&"'!C:C");"<>"))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&SHEETS&"'!A:A");A3;INDIRECT("'"&SHEETS&"'!B:B");"<>x";INDIRECT("'"&SHEETS&"'!C:C");"<>"))

Thanks in advance.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

The biggest problem with your formula, is that you are using whole columns, you should limit the range to more realistic size like

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SHEETS&"'!H2:H1000");INDIRECT("'"&SHEETS&"'!A2:A1000");A3;INDIRECT("'"&SHEETS&"'!B2:B1000");"<>x";INDIRECT("'"&SHEETS&"'!C2:C1000");"<>"))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&SHEETS&"'!A2:A1000");A3;INDIRECT("'"&SHEETS&"'!B2:B1000");"<>x";INDIRECT("'"&SHEETS&"'!C2:C1000");"<>"))

 

[Shanaya2009]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

The biggest problem with your formula, is that you are using whole columns, you should limit the range to more realistic size like

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SHEETS&"'!H2:H1000");INDIRECT("'"&SHEETS&"'!A2:A1000");A3;INDIRECT("'"&SHEETS&"'!B2:B1000");"<>x";INDIRECT("'"&SHEETS&"'!C2:C1000");"<>"))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&SHEETS&"'!A2:A1000");A3;INDIRECT("'"&SHEETS&"'!B2:B1000");"<>x";INDIRECT("'"&SHEETS&"'!C2:C1000");"<>"))

Thanks for the tip about my settings.

And mostly, thanks for your help. Such a simple solution, that actually works
(why didn't I think of that )

 

[Fluff]

Glad to help. Thanks for the feedback & updating your profile.