Lookup / count ?

**palina** · 04-23-2007, 01:31 PM

Hi all

I am having problems with implementing a function in my excel sheet. Pls see the example / table:

Sweden..............yes
Denmark .............no
Uk .....................yes
Italy ..................yes
Spain .................no
Belgium ..............yes
Sweden .............yes
Denmark .............no
Uk .....................no
Italy ..................yes
Spain .................no
Belgium ...............no

and so forth... (countries always in the same order and new lines constantly being added to the document)

What I want to be able to do is to count all the yes's for the different countries. I.e. in this case: Sweden 2, Denmark 0, UK 1 etc...

I have tried this with lookup and countif but cannot get it to work.

I highly appreciate any help.
Thanks in advance
Palina

**BigBas** · 04-23-2007, 01:34 PM

You have more than one variable, so countif will not work.

Try this:

=SUMPRODUCT(--(A1:A100="DENMARK"),(--B1:B100="YES"))

Obviously, adjust your range to suit.

**VBA Noob** · 04-23-2007, 01:36 PM

Prehaps

=SUMPRODUCT(--($A$1:$A$12="Sweden")*($B$1:$B$12="yes"))

or if Sweden is in say cell C1

=SUMPRODUCT(--($A$1:$A$12=C1)*($B$1:$B$12="yes"))

VBA Noob