I need to multiply the result not sum ie SUMIFS

[jmillf]

Hi please see attached screenshot.

I need to multiply the results instead of summing the results. I currently get the correct variables summing, just need a hand to multiply.

=SUMIFS(Y73:Y850,C73:C850,AF74,F73:F850,"DS")

Help pls

Thanks
Jarrad

 

[jmillf]

Thank you

 

[Peter_SSs]

If I have understood correctly, then your existing formula was fairly close. It just needed to lock the rows being calculated with an absolute row address ($ sign) and to use column F for the shift rather than hard-code the "DS"

=IFERROR(SUMIFS(Y$74:Y$850,C$74:C$850,AH74,F$74:F$850,F74)/(COUNTIFS(C$74:C$850, AH74,F$74:F$850,F74)),"NA")

If you are using a recent version of Excel (good idea to update your profile to include this info so helpers know) and have the AVERAGEIFS function then you could use that

=AVERAGEIFS(Y$74:Y$850,C$74:C$850,AH74,F$74:F$850,F74)

Yet another option would be to use a Pivot Table (assuming columns C:Y all have headings in row 72):

Book1
	A	B	C	D	E
1	Average of OEE	Column Labels
2	Row Labels	AS	DS	NS
3	18-Jun	79.18%	32.35%	84.90%
4	2-Jul	33.70%	48.53%
5
Sheet3

 

[jmillf]

If I have understood correctly, then your existing formula was fairly close. It just needed to lock the rows being calculated with an absolute row address ($ sign) and to use column F for the shift rather than hard-code the "DS"

=IFERROR(SUMIFS(Y$74:Y$850,C$74:C$850,AH74,F$74:F$850,F74)/(COUNTIFS(C$74:C$850, AH74,F$74:F$850,F74)),"NA")

If you are using a recent version of Excel (good idea to update your profile to include this info so helpers know) and have the AVERAGEIFS function then you could use that

=AVERAGEIFS(Y$74:Y$850,C$74:C$850,AH74,F$74:F$850,F74)

Yet another option would be to use a Pivot Table (assuming columns C:Y all have headings in row 72):

Book1
	A	B	C	D	E
1	Average of OEE	Column Labels
2	Row Labels	AS	DS	NS
3	18-Jun	79.18%	32.35%	84.90%
4	2-Jul	33.70%	48.53%
5
Sheet3

Perfect!!!! Thank you very much. Used the AVERAGEIFS statement. Thanks again

 

[Peter_SSs]

Perfect!!!! Thank you very much. Used the AVERAGEIFS statement. Thanks again

Cheers. Thanks for letting us know.

(Don't forget to update your profile)

 

[jmillf]

Done. thanks Peter