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!

On Oct 14, 10:16*am, 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!

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

On Oct 14, 1:37*pm, jlclyde wrote:
On Oct 14, 10:16*am, 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!

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!

Hello,

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

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

Regards,
Bernd

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 MVP
www.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!

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

--
__________________________________
HTH

Bob

"LindsE" wrote in message
...
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!

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

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 MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

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!