Remember Me?

#1
June 28th 11, 08:13 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2011 Posts: 2
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'!D71000="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?

#2
June 28th 11, 10:19 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2011 Posts: 2
Nesting SUBTOTAL with SUMPRODUCT?

Here is a solution for anyone that could use it.

from a gentleman at another board:

=SUMPRODUCT(--(C7:C1000="Europe"),--(D71000="Equity"),--(E7:E1000="Urgent"),--(F7:F1000="Inactive"),--(W7:W1000<"ERROR"),J7:J1000) / SUMPRODUCT(--(C7:C1000="Europe"),--(D71000="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,"Equity",E:E,"Urg ent",F:F,"Inactive",W:W,"ERROR")

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post sj2008 Excel Worksheet Functions 10 January 3rd 09 12:50 AM SteveDB1 Excel Worksheet Functions 9 August 27th 08 11:38 PM Twishlist Excel Worksheet Functions 3 July 26th 07 07:53 AM Nimish Shah Excel Worksheet Functions 6 December 22nd 06 01:27 PM Greenebush Excel Worksheet Functions 4 March 17th 06 10:35 PM

All times are GMT +1. The time now is 11:29 AM.