Counting list, while checking for criteria

[mako]

Hi

I need help finding a function that will count the number of countries on my list that match other criteria, but not counting any country twice.

I hope someone can be of assistance. Thanks.

-Mako-

[Bryan Hessey]

Hi

I need help finding a function that will count the number of countries on my list that match other criteria, but not counting any country twice.

I hope someone can be of assistance. Thanks.

-Mako-

Hi,

in a helper column (say D, in D2) put

=IF(AND(ISNUMBER(C2),C2>0,SUMPRODUCT(--(C$2:C2=C2),--(C$2:C2<>"-"))=1),1,"")

and formula fill that to the extent of your data.

The total =Sum(D:D) should be your total.

hth
---

[mako]

No, that does help unfortunately.

How can I explain my needs better? Let's say that only the countries receiving a grant were on the list, then I need the number of countries = no country twice.

So the function must first check whether the each row has received a grant, and then if the country is already on the list.

I hope this helps. Thank you for you quick reply though...

-Mako-

[Bryan Hessey]

No, that does help unfortunately.

How can I explain my needs better? Let's say that only the countries receiving a grant were on the list, then I need the number of countries = no country twice.

So the function must first check whether the each row has received a grant, and then if the country is already on the list.

I hope this helps. Thank you for you quick reply though...

-Mako-

Hi,

wrong version,

try

=IF(AND(ISNUMBER(C2),C2>0,SUMPRODUCT(--(A$2:A2=A2),--(C$2:C2<>"-"))=1),1,"")

---

btw, a number of your Countries are followed by a space, like Cameroon , Ghana , Kenya , Rwanda , Senegal etc, this is not a good idea when trying to match items.
=Trim(A1) will remove those, perhaps worth noting when you load items.
---

[mako]

Thanks a million, I added a column for the results of your function and =sum'ed it. Then I just hid it, so it doesn't confuse anyone.

About the spaces after the names, yes you're right, I wasn't aware of it.

[Bryan Hessey]

Thanks a million, I added a column for the results of your function and =sum'ed it. Then I just hid it, so it doesn't confuse anyone.

About the spaces after the names, yes you're right, I wasn't aware of it.

good to see that it worked for you, and thanks for the feedback.

---