Counting with Conditions but Only Once

[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 (D22000 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!

 

[jlclyde]

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 (D22000 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!

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

 

[LindsE]

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 -

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!

 

[Bernd P]

Hello,

Array-enter
=COUNT(Pstat("Count",(L2:L3000="M")*(D23000=8)*(E2:E3000<=12)*
(E2:E3000>=6),B2:B3000))

Pstat you can get here:
http://sulprobil.com/html/pstat.html

Regards,
Bernd

 

[Domenic]

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.

 

[Bob Phillips]

Try this ARRAY formula

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

 

[LindsE]

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 -

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!!

 

[Domenic]

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!!

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.

 

[LindsE]

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 -

Wonderful! Thanks again!