how to count unique values in excel based on multiple criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

IDBUGM - 15 Mar 2006 16:30 GMT

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 to this Message

SteveG - 15 Mar 2006 16:46 GMT

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

=SUMPRODUCT((A2:A10="Edgar")*(D2:D10>DATE(2006,2,14))*(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

Signature

SteveG

Reply to this Message

Ron Coderre - 15 Mar 2006 16:50 GMT

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

> 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,
[quoted text clipped - 16 lines]
>
> Thanks

Reply to this Message

Tom Ogilvy - 15 Mar 2006 17:00 GMT

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

Signature

Regards,
Tom Ogilvy

> 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,
[quoted text clipped - 16 lines]
>
> Thanks

Reply to this Message