Countifs + OR function

[Espenjk]

Hi guys,

I need help with the following problem: I want to use a countifs function to search for a given criteria in a column A, and then find how many tokens correspond to this criteria given that one of several conditions are met in column B. This is probably not a good explanation, and I would attach an example file for you, but for some reason nothing happens when I press the attachment icon, is there a bug here or something? Used to work before...

Anyway, say column A contains names and column B contains nationalities. I want to find out how many people are called Jack that come from either England, Scotland or the USA. So, I use countifs: I specify the first domain as A, the first criterion as Jack, then the second domain as B, and then...? How do I specify "either England, Scotland OR the USA?"

Please help me.

Thanks in advance,

Espen.

[etaf]

you cannot do an OR in a countf or sumif
its an AND function

you could just do 3 separate countifs() and use +

=countifs( A:A , "jack", B:B, "england" ) + countifs( A:A , "jack", B:B, "Scotland" ) +countifs( A:A , "jack", B:B, "USA" )

probably a better way to do this

the add attachment - works for me

goto advanced and then manage attachments

[AlKey]

Try this

Please Login or Register  to view this content.

[Tony Valko]

One way...

Data Range

	A	B	C	D	E	F
1	Paul	France		Jack	England	3
2	Jack	USA			Scotland
3	Jack	Germany			USA
4	Tim	Mexico
5	Sue	Canada
6	Lisa	USA
7	Jack	Scotland
8	Jack	France
9	Lynn	USA
10	Jack	England
11	------	------	------	------	------	------

This formula entered in F1:

=SUMPRODUCT(COUNTIFS(A1:A10,D1,B1:B10,E1:E3))

[Espenjk]

Thanks for all your kind help, Tony's suggestion worked fine for me. Very useful for the future!

[Tony Valko]

You're welcome. Thanks for the feedback!