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?

You can use something like this in E2
=SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100>C2),-(D2100<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?
>

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:C100>C2),-(D2100<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?
> >

That should read
=SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100>C2),--(D2100<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
news:AF9A6CC7-F9D7-4E2B-AD3B-(E-Mail Removed)...
> You can use something like this in E2
> =SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100>C2),-(D2100<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?
>>

Thanks Bernard,

Sorry for the typo...

-Sheeloo

"Bernard Liengme" wrote:

> That should read
> =SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100>C2),--(D2100<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
> news:AF9A6CC7-F9D7-4E2B-AD3B-(E-Mail Removed)...
> > You can use something like this in E2
> > =SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100>C2),-(D2100<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?
> >>
>
>
>

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:C100>C2),-(D2100<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?
> > >

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

"Bernard Liengme" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> That should read
> =SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100>C2),--(D2100<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
> news:AF9A6CC7-F9D7-4E2B-AD3B-(E-Mail Removed)...
>> You can use something like this in E2
>> =SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100>C2),-(D2100<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?
>>>
>
>

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:C100>C2),-(D2100<D2)) ?
> Isn't it OK provided that the total number of negations is even?
> --
> David Biddulph
>
> "Bernard Liengme" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > That should read
> > =SUMPRODUCT(--(A2:A100=A2),--(B2:B100=B2),--(C2:C100>C2),--(D2100<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
> > news:AF9A6CC7-F9D7-4E2B-AD3B-(E-Mail Removed)...
> >> You can use something like this in E2
> >> =SUMPRODUCT(--(A2:A100=A2),-(B2:B100=B2),--(C2:C100>C2),-(D2100<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?
> >>>
> >
> >
>
>
>

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:C100>C2),-(D2100<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?
> > > >

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),-(D2100=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:C100>C2),-(D2100<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?
> > > > >