ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Nesting SUBTOTAL with SUMPRODUCT? (https://www.excelbanter.com/excel-worksheet-functions/270855-nesting-subtotal-sumproduct.html)

Corey Smith

Nesting SUBTOTAL with SUMPRODUCT?
 
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(--('RAW DATA'!C7:C1000="Europe"),--('RAW DATA'!D7:D1000="Equity"),--('RAW DATA'!E7:E1000="Urgent"),--('RAW DATA'!F7:F1000="Inactive"),--('RAW DATA'!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.

Any suggestions?



Corey Smith

Nesting SUBTOTAL with SUMPRODUCT?
 
Here is a solution for anyone that could use it.

from a gentleman at another board:

=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,"Urg ent",F:F,"Inactive",W:W,"ERROR")





All times are GMT +1. The time now is 01:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com