Counting unique text that meets several criteria
 

I am looking to count the number of unique text cells in Col. A that meet
several criteria across Col. B-F.

And I'll want to do this calculation numerous times with changing criteria
(to fill out a large table).

Any ideas?

Counting unique text that meets several criteria
 

You can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2))
This will give you the count of rows where Col A has the value A2, Col B has
B2, Col C has C2 and Col D has D2.

You can add more terms for other columns...

"blswes" wrote:

I am looking to count the number of unique text cells in Col. A that meet
several criteria across Col. B-F.

And I'll want to do this calculation numerous times with changing criteria
(to fill out a large table).

Any ideas?

Counting unique text that meets several criteria
 

Hmmmm, doesn't seem to be returning the results I want.

If I have 14 text entries of "House" that all meet the criteria that I set,
I want the result to be 1 (unique entry).

So I'm looking for a way (like with Sumproduct) to set multiple criteria for
Col. A but also to identify how many unique entries satisfy those criteria
since there are many duplicates.

Does that help?

"Sheeloo" wrote:

You can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2))
This will give you the count of rows where Col A has the value A2, Col B has
B2, Col C has C2 and Col D has D2.

You can add more terms for other columns...

"blswes" wrote:

I am looking to count the number of unique text cells in Col. A that meet
several criteria across Col. B-F.

And I'll want to do this calculation numerous times with changing criteria
(to fill out a large table).

Any ideas?

Counting unique text that meets several criteria
 

That should read
=SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100C2),--(D2:D100<D2))
with double negations before all open parentheses.
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Sheeloo" <Click above to get my email id wrote in message
...
You can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2))
This will give you the count of rows where Col A has the value A2, Col B
has
B2, Col C has C2 and Col D has D2.

You can add more terms for other columns...

"blswes" wrote:

I am looking to count the number of unique text cells in Col. A that meet
several criteria across Col. B-F.

And I'll want to do this calculation numerous times with changing
criteria
(to fill out a large table).

Any ideas?

Counting unique text that meets several criteria
 

Thanks Bernard,

Sorry for the typo...

-Sheeloo

"Bernard Liengme" wrote:

That should read
=SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100C2),--(D2:D100<D2))
with double negations before all open parentheses.
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Sheeloo" <Click above to get my email id wrote in message
...
You can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2))
This will give you the count of rows where Col A has the value A2, Col B
has
B2, Col C has C2 and Col D has D2.

You can add more terms for other columns...

"blswes" wrote:

I am looking to count the number of unique text cells in Col. A that meet
several criteria across Col. B-F.

And I'll want to do this calculation numerous times with changing
criteria
(to fill out a large table).

Any ideas?

Counting unique text that meets several criteria
 

Not sure what you want... Pl. provide sample data.

"blswes" wrote:

Hmmmm, doesn't seem to be returning the results I want.

If I have 14 text entries of "House" that all meet the criteria that I set,
I want the result to be 1 (unique entry).

So I'm looking for a way (like with Sumproduct) to set multiple criteria for
Col. A but also to identify how many unique entries satisfy those criteria
since there are many duplicates.

Does that help?

"Sheeloo" wrote:

You can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2))
This will give you the count of rows where Col A has the value A2, Col B has
B2, Col C has C2 and Col D has D2.

You can add more terms for other columns...

"blswes" wrote:

I am looking to count the number of unique text cells in Col. A that meet
several criteria across Col. B-F.

And I'll want to do this calculation numerous times with changing criteria
(to fill out a large table).

Any ideas?

Counting unique text that meets several criteria
 

Does it matter, Bernard? Couldn't you also get away with
=SUMPRODUCT(-(A2:A100=A2),-(B2:B100=B2),-(C2:C100C2),-(D2:D100<D2)) ?
Isn't it OK provided that the total number of negations is even?
--
David Biddulph

"Bernard Liengme" wrote in message
...
That should read
=SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100C2),--(D2:D100<D2))
with double negations before all open parentheses.
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Sheeloo" <Click above to get my email id wrote in message
...
You can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2))
This will give you the count of rows where Col A has the value A2, Col B
has
B2, Col C has C2 and Col D has D2.

You can add more terms for other columns...

"blswes" wrote:

I am looking to count the number of unique text cells in Col. A that
meet
several criteria across Col. B-F.

And I'll want to do this calculation numerous times with changing
criteria
(to fill out a large table).

Any ideas?

Counting unique text that meets several criteria
 

You are right David :-)

Unfortunately it was not intentional in this case... It was a typo on my part.

"David Biddulph" wrote:

Does it matter, Bernard? Couldn't you also get away with
=SUMPRODUCT(-(A2:A100=A2),-(B2:B100=B2),-(C2:C100C2),-(D2:D100<D2)) ?
Isn't it OK provided that the total number of negations is even?
--
David Biddulph

"Bernard Liengme" wrote in message
...
That should read
=SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100C2),--(D2:D100<D2))
with double negations before all open parentheses.
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Sheeloo" <Click above to get my email id wrote in message
...
You can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2))
This will give you the count of rows where Col A has the value A2, Col B
has
B2, Col C has C2 and Col D has D2.

You can add more terms for other columns...

"blswes" wrote:

I am looking to count the number of unique text cells in Col. A that
meet
several criteria across Col. B-F.

And I'll want to do this calculation numerous times with changing
criteria
(to fill out a large table).

Any ideas?

Counting unique text that meets several criteria
 

Here would be a basic example:

Col. A Col. B Col. C Col. D
Tree 40 Yes 1
Tree 45 Yes 1
Tree 45 Yes 1
Leaf 43 Yes 1
Leaf 44 Yes 1

What I'm looking for is unique entries that satisfy my criteria.

So if I want unique Col. A entries that are 35 (Col. B), that have "Yes"
(Col. C), and that equal 1 (Col. D), the answer is 2.

But if I'm unable to get unique Col. A entries, then the answer with
Sumproduct would be 5, which is not what I'm seeking.

Does that help?

Thanks in advance.

"Sheeloo" wrote:

Not sure what you want... Pl. provide sample data.

"blswes" wrote:

Hmmmm, doesn't seem to be returning the results I want.

If I have 14 text entries of "House" that all meet the criteria that I set,
I want the result to be 1 (unique entry).

So I'm looking for a way (like with Sumproduct) to set multiple criteria for
Col. A but also to identify how many unique entries satisfy those criteria
since there are many duplicates.

Does that help?

"Sheeloo" wrote:

You can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2))
This will give you the count of rows where Col A has the value A2, Col B has
B2, Col C has C2 and Col D has D2.

You can add more terms for other columns...

"blswes" wrote:

I am looking to count the number of unique text cells in Col. A that meet
several criteria across Col. B-F.

And I'll want to do this calculation numerous times with changing criteria
(to fill out a large table).

Any ideas?

Counting unique text that meets several criteria
 

Now I understand what you want..

You need to add a column to mark 1,2,3, against each entry in Col A - 1
against first occurence, two against the second and so on...

In your example enter this in E2
=COUNTIF($A$2:A2,A2)
and copy down...
Starting at row 2 assuming headers in row 1

Now use SUMPRODUCT in F1 like this
=SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100=C2),-(D2:D100=1))

"blswes" wrote:

Here would be a basic example:

Col. A Col. B Col. C Col. D
Tree 40 Yes 1
Tree 45 Yes 1
Tree 45 Yes 1
Leaf 43 Yes 1
Leaf 44 Yes 1

What I'm looking for is unique entries that satisfy my criteria.

So if I want unique Col. A entries that are 35 (Col. B), that have "Yes"
(Col. C), and that equal 1 (Col. D), the answer is 2.

But if I'm unable to get unique Col. A entries, then the answer with
Sumproduct would be 5, which is not what I'm seeking.

Does that help?

Thanks in advance.

"Sheeloo" wrote:

Not sure what you want... Pl. provide sample data.

"blswes" wrote:

Hmmmm, doesn't seem to be returning the results I want.

If I have 14 text entries of "House" that all meet the criteria that I set,
I want the result to be 1 (unique entry).

So I'm looking for a way (like with Sumproduct) to set multiple criteria for
Col. A but also to identify how many unique entries satisfy those criteria
since there are many duplicates.

Does that help?

"Sheeloo" wrote:

You can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2))
This will give you the count of rows where Col A has the value A2, Col B has
B2, Col C has C2 and Col D has D2.

You can add more terms for other columns...

"blswes" wrote:

I am looking to count the number of unique text cells in Col. A that meet
several criteria across Col. B-F.

And I'll want to do this calculation numerous times with changing criteria
(to fill out a large table).

Any ideas?

Counting unique text that meets several criteria
 

Missed a - again :-(

Use
=SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100=C2),--(D2:D100=1))

instead of
=SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100=C2),-(D2:D100=1))
which will give you a negative count... due to single - in -(D2:D100=1)

"Sheeloo" wrote:

Now I understand what you want..

You need to add a column to mark 1,2,3, against each entry in Col A - 1
against first occurence, two against the second and so on...

In your example enter this in E2
=COUNTIF($A$2:A2,A2)
and copy down...
Starting at row 2 assuming headers in row 1

Now use SUMPRODUCT in F1 like this
=SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100=C2),-(D2:D100=1))

"blswes" wrote:

Here would be a basic example:

Col. A Col. B Col. C Col. D
Tree 40 Yes 1
Tree 45 Yes 1
Tree 45 Yes 1
Leaf 43 Yes 1
Leaf 44 Yes 1

What I'm looking for is unique entries that satisfy my criteria.

So if I want unique Col. A entries that are 35 (Col. B), that have "Yes"
(Col. C), and that equal 1 (Col. D), the answer is 2.

But if I'm unable to get unique Col. A entries, then the answer with
Sumproduct would be 5, which is not what I'm seeking.

Does that help?

Thanks in advance.

"Sheeloo" wrote:

Not sure what you want... Pl. provide sample data.

"blswes" wrote:

Hmmmm, doesn't seem to be returning the results I want.

If I have 14 text entries of "House" that all meet the criteria that I set,
I want the result to be 1 (unique entry).

So I'm looking for a way (like with Sumproduct) to set multiple criteria for
Col. A but also to identify how many unique entries satisfy those criteria
since there are many duplicates.

Does that help?

"Sheeloo" wrote:

You can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100C2),-(D2:D100<D2))
This will give you the count of rows where Col A has the value A2, Col B has
B2, Col C has C2 and Col D has D2.

You can add more terms for other columns...

"blswes" wrote:

I am looking to count the number of unique text cells in Col. A that meet
several criteria across Col. B-F.

And I'll want to do this calculation numerous times with changing criteria
(to fill out a large table).

Any ideas?