Countif

[Muppet]

Hi,

Help needed if someone could be so kind.

Have 2 columns, each cell has 4 different options to choose from ( a
drop down list with 4 colours for example).

On another sheet I want to say... count as 1 if column 1= Green and
column 2=blue.

I know that for one it would be =COUNTIF(Sheet1!C4:C203,"Green")

but how do i say.. coutif Green in 1 column and blue in the next?

.. so be clear I dont want to add them;
=COUNTIF(Sheet1!C4:C203,"Green")+=COUNTIF(Sheet1!C4:C203,"Blue") as
that would count as 2. I want to say when these two come together count
as 1.


Thanks in anticipation,

Muppet

 

[BenjieLop]

Hi,

Help needed if someone could be so kind.

Have 2 columns, each cell has 4 different options to choose from (
drop down list with 4 colours for example).

On another sheet I want to say... count as 1 if column 1= Green an
column 2=blue.

I know that for one it would be =COUNTIF(Sheet1!C4:C203,"Green")

but how do i say.. coutif Green in 1 column and blue in the next?

.. so be clear I dont want to add them
=COUNTIF(Sheet1!C4:C203,"Green")+=COUNTIF(Sheet1!C4:C203,"Blue") a
that would count as 2. I want to say when these two come together coun
as 1.


Thanks in anticipation,

Muppet

Assuming that your 2 columns are A4:A203 and B4:B203, your formula is

=SUMPRODUCT((A4:A203=\"BLUE\")*(B4:B203=\"GREEN\")

Hope this is what you need.

Regards

 

[Dave Peterson]

=sumproduct(--(sheet1!c4:c203="green"),--(sheet1!d4:d203="blue"))

maybe???

=sumproduct() likes to work with numbers. The -- stuff changes trues/falses to
1's and 0's.