Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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")





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nesting SUMIF within SUMPRODUCT sj2008 Excel Worksheet Functions 10 January 3rd 09 12:50 AM
nesting sumproduct with subtotal SteveDB1 Excel Worksheet Functions 9 August 27th 08 11:38 PM
Average nesting within Sumproduct? Twishlist Excel Worksheet Functions 3 July 26th 07 07:53 AM
Excel : Nesting of functions such as sumproduct and sumif Nimish Shah Excel Worksheet Functions 6 December 22nd 06 01:27 PM
subtotal nesting errors new this month Greenebush Excel Worksheet Functions 4 March 17th 06 10:35 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017