Nesting SUBTOTAL with SUMPRODUCT?

[ctsmith84]

Hello,

I am working on a spreadsheet app for a department here at work, and I need get the count of rows given multiple criteria...but then also need to get the average of the values in another column, of those same rows.

I have this so far to get the count of rows:

=SUMPRODUCT(--(C7:C1000="Europe"),--(D7:D1000="Equity"),--(E7:E1000="Urgent"),--(F7:F1000="Inactive"),--(W7:W1000<>"ERROR"))

Now I just need to find a way to make another function that takes the rows produced by that, and gets an average of the values in column J. I'm thinking that I can somehow nest a SUBTOTAL in there, but I can't get it to work. Would I need something like the following inside of the SUMPRODUCT:

=SUBTOTAL(1,'RAW DATA'!J7:J1000)

Any suggestions would be greatly appreciated! <!-- google_ad_section_end -->

 

[MrKowz]

Welcome to the forums!

You'll need to actually divide using two separate SUMPRODUCTS. One that SUMS those values, another that counts how many meet the criteria:

=SUMPRODUCT(--(C7:C1000="Europe"),--(D7:D1000="Equity"),--(E7:E1000="Urgent"),--(F7:F1000="Inactive"),--(W7:W1000<>"ERROR"),J7:J1000) / SUMPRODUCT(--(C7:C1000="Europe"),--(D7:D1000="Equity"),--(E7:E1000="Urgent"),--(F7:F1000="Inactive"),--(W7:W1000<>"ERROR"))

Or if you use Excel 2007 or newer:

=AVERAGEIFS(J:J,C:C,"Europe",D:D,"Equity",E:E,"Urgent",F:F,"Inactive",W:W,"ERROR")

 

[ctsmith84]

=AVERAGEIFS(J:J,C:C,"Europe",D:D,"Equity",E:E,"Urgent",F:F,"Inactive",W:W,"ERROR")

This one right here is the winner! You're the man, thank you very much!

 

[MrKowz]

This one right here is the winner! You're the man, thank you very much!

Happy to be of service. Thank you for the feedback!

 

[ctsmith84]

Maybe you can help me again.

Is it possible to use AVERAGEIFS if you need to put multiple criterias on one range? For example, I have this:

=AVERAGEIFS(J:J,F:F,"Inactive", D:D,"Fixed Income")

...and I need to add another criteria for column D, for "Equity".

i've tried putting an OR, tried adding D:D,"Equity" to the end...i get an error with both.

Any suggestions?

 

[ctsmith84]

The error code that it's shooting is the #DIV/0!.

But if calculated right, the denominator shouldn't be 0.