On Oct 15, 1:49*pm, Domenic wrote:
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.

In article
,





*LindsE wrote:
On Oct 14, 2:07*pm, Domenic wrote:
Try...

=SUM(IF(FREQUENCY(IF(($L$2:$L$2000="M")*($D$2:$D$2 000=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

In article
,

*LindsE wrote:
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!- Hide quoted text -

- 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
Microsoft Excel MVPwww.xl-central.com
Your Quick Reference to Excel Solutions- Hide quoted text -

- Show quoted text -

Wonderful! Thanks again!