Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
IDBUGM
 
Posts: n/a
Default how to count unique values in excel based on multiple criteria

I need to count and sum a worksheet of over 10,000 rows based on
multiple criteria. 1st is by the employee name, second date range,
they other is product type, but for this example i doubt three is much
different than two criteria's.

Employee Price Qty Date
Tom 69 1 14-Feb
Edgar 34 1 14-Feb
Tom 55 1 10-Feb
David 25 1 28-Feb
Edgar 59 1 1-Feb
David 280 -1 20-Feb
Tom 355 1 15-Feb
Edgar 125 1 17-Feb
Edgar 175 1 3-Feb

How could I count the QTY if the employee is Edgar between 2/14 and
2/28? Any help would be greatly appriciated.

Thanks

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SteveG
 
Posts: n/a
Default how to count unique values in excel based on multiple criteria


SUMPRODUCT will work. Assuming your table is in A1:D10.

=SUMPRODUCT((A2:A10="Edgar")*(D2:D10DATE(2006,2,1 4))*(D2:D10<DATE(2006,2,28))*(C2:C10))

If you want to include the 14th and 28th in your calc then,

=SUMPRODUCT((A2:A10="Edgar")*(D2:D10=DATE(2006,2, 14))*(D2:D10<=DATE(2006,2,28))*(C2:C10))

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=522658

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default how to count unique values in excel based on multiple criteria

Try this:

With your sample data in A1:D10

G1: Edgar (the name)
G2: 02/14/2006 (the start date)
G3: 02/28/2006 (the end date)
H1:
=SUMPRODUCT(($A$2:$A$10=G1)*($D$2:$D$10=G2)*($D$2 :$D$10<=G3)*($C$2:$C$10))

In that instance, H1 returns 2

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"IDBUGM" wrote:

I need to count and sum a worksheet of over 10,000 rows based on
multiple criteria. 1st is by the employee name, second date range,
they other is product type, but for this example i doubt three is much
different than two criteria's.

Employee Price Qty Date
Tom 69 1 14-Feb
Edgar 34 1 14-Feb
Tom 55 1 10-Feb
David 25 1 28-Feb
Edgar 59 1 1-Feb
David 280 -1 20-Feb
Tom 355 1 15-Feb
Edgar 125 1 17-Feb
Edgar 175 1 3-Feb

How could I count the QTY if the employee is Edgar between 2/14 and
2/28? Any help would be greatly appriciated.

Thanks


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Tom Ogilvy
 
Posts: n/a
Default how to count unique values in excel based on multiple criteria

=sumproduct(--(A1:A10000="Edgar"),--(D1:D10000=DateValue("2/14/2006")),--(D1:D10000<=DateValue("2/28/2006")),C1:C10000)

--
Regards,
Tom Ogilvy


"IDBUGM" wrote:

I need to count and sum a worksheet of over 10,000 rows based on
multiple criteria. 1st is by the employee name, second date range,
they other is product type, but for this example i doubt three is much
different than two criteria's.

Employee Price Qty Date
Tom 69 1 14-Feb
Edgar 34 1 14-Feb
Tom 55 1 10-Feb
David 25 1 28-Feb
Edgar 59 1 1-Feb
David 280 -1 20-Feb
Tom 355 1 15-Feb
Edgar 125 1 17-Feb
Edgar 175 1 3-Feb

How could I count the QTY if the employee is Edgar between 2/14 and
2/28? Any help would be greatly appriciated.

Thanks


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
Count of Unique values Richard Excel Worksheet Functions 3 September 27th 05 05:46 AM
count based on multiple date criteria lisaw Excel Worksheet Functions 1 August 9th 05 05:31 PM
Sum Count of Single Criteria in Multiple Non-Adjacent columns Sam via OfficeKB.com Excel Worksheet Functions 9 July 14th 05 10:01 PM
how to count unique entries with multiple condition Michael Excel Worksheet Functions 6 June 29th 05 12:38 PM
Returning Multiple Values Based on One Value Nick Excel Worksheet Functions 2 March 31st 05 10:01 PM


All times are GMT +1. The time now is 07:51 PM.

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

About Us

"It's about Microsoft Excel"