Counting with Conditions but Only Once

  • Thread starter Thread starter LindsE
  • Start date Start date
L

LindsE

Hi there;

I'm looking for a way to tally up data that matches certain criteria.
Here's my scenario:
Each object has Given Data:
1) ID Number (B2:B2000 of spreadsheet)
2) Size (L2:L2000; can be S M L XL)
3) Month (D2:D2000 can be 08 or 09)
4) Day (E2:E2000 can be any 01 - 31)

Each ID appears several times with various data associated. I want to
count each ID just ONCE for the conditions:
Dates: August 6 - 12
Size: Medium

I can count every occurrence of the ID by using SUMPRODUCT as follows:
=SUMPRODUCT(--($L$2:$L$2000="M"),--($D$2:$D$2000=8),--($E$2:$E
$1906>=6),--($E$2:$E$1906<=12))

How can I include the condition that each ID in column B can only be
counted once?

Many many thanks!
 
Hi there;

I'm looking for a way to tally up data that matches certain criteria.
Here's my scenario:
Each object has Given Data:
1) ID Number (B2:B2000 of spreadsheet)
2) Size (L2:L2000; can be S M L XL)
3) Month (D2:D2000 can be 08 or 09)
4) Day (E2:E2000 can be any 01 - 31)

Each ID appears several times with various data associated.  I want to
count each ID just ONCE for the conditions:
Dates: August 6 - 12
Size: Medium

I can count every occurrence of the ID by using SUMPRODUCT as follows:
=SUMPRODUCT(--($L$2:$L$2000="M"),--($D$2:$D$2000=8),--($E$2:$E
$1906>=6),--($E$2:$E$1906<=12))

How can I include the condition that each ID in column B can only be
counted once?

Many many thanks!
Click to expand...

Use a helper column and do a countif =COUNTIF(B$2:B2,B2) and drag
down. this way it is counting how many times the condition was met.
Then you just add this into your sumproduct. I hope I am making
myself clear.
Jay
 
Use a helper column and do a countif =COUNTIF(B$2:B2,B2) and drag
down.  this way it is counting how many times the condition was met.
Then you just add this into your sumproduct.  I hope I am making
myself clear.
Jay- Hide quoted text -

- Show quoted text -
Click to expand...

Hi there;

Thank you for your response. I have added the column, but I'm not
sure how to add this into the sumproduct. Could you please elaborate?

Thanks again!
 
Try...

=SUM(IF(FREQUENCY(IF(($L$2:$L$2000="M")*($D$2:$D$2000=8)*($E$2:$E$2000>=6
)*($E$2:$E$2000<=12)*($B$2:$B$2000<>""),MATCH("~"&$B$2:$B$2000,$B$2:$B$20
00&"",0)),ROW($B$2:$B$2000)-ROW($B$2)+1),1))

....confirmed with CONTROL+SHIFT+ENTER.
 
Try this ARRAY formula

=COUNT(1/FREQUENCY(IF((D2:D2000=8)*(E2:E2000>=6)*(E2:E2000<=12)*(L2:L2000="M"),
IF(A2:A100<>"",A2:A100)),IF((D2:D2000=8)*(E2:E2000>=6)*(E2:E2000<=12)*(L2:L2000="M"),IF(A2:A2000<>"",A2:A2000))))
 
Try...

=SUM(IF(FREQUENCY(IF(($L$2:$L$2000="M")*($D$2:$D$2000=8)*($E$2:$E$2000>=6
)*($E$2:$E$2000<=12)*($B$2:$B$2000<>""),MATCH("~"&$B$2:$B$2000,$B$2:$B$20
00&"",0)),ROW($B$2:$B$2000)-ROW($B$2)+1),1))

...confirmed with CONTROL+SHIFT+ENTER.

--
Domenic
Microsoft Excel MVPwww.xl-central.com
Your Quick Reference to Excel Solutions











- Show quoted text -
Click to expand...

Thank you all;

Dominic: I understand this one best of the suggestions given. Could
you please explain what the tilde with the ampersand does in the MATCH
command?

Thank you SO much!!
 
Thank you all;
Dominic: I understand this one best of the suggestions given. Could
you please explain what the tilde with the ampersand does in the MATCH
command?

Thank you SO much!!
Click to expand...

The tilde is an escape character. It allows wild characters, such as *
and ? to be recognized as a regular character. The &"" converts each
value into a text value.
 
The tilde is an escape character.  It allows wild characters, such as *
and ? to be recognized as a regular character.   The &"" converts each
value into a text value.











--
Domenic
Microsoft Excel MVPwww.xl-central.com
Your Quick Reference to Excel Solutions- Hide quoted text -

- Show quoted text -
Click to expand...

Wonderful! Thanks again!
 
Back
Top