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!C 4:C203,"Blue") as
that would count as 2. I want to say when these two come together count
as 1.


Thanks in anticipation,

Muppet


--
Muppet
------------------------------------------------------------------------
Muppet's Profile: http://www.excelforum.com/member.php...o&userid=10989
View this thread: http://www.excelforum.com/showthread...hreadid=394969

Muppet Wrote:
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!C 4:C203,"Blue") as
that would count as 2. I want to say when these two come together count
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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=394969

=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.



Muppet wrote:

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!C 4:C203,"Blue") as
that would count as 2. I want to say when these two come together count
as 1.

Thanks in anticipation,

Muppet

--
Muppet
------------------------------------------------------------------------
Muppet's Profile: http://www.excelforum.com/member.php...o&userid=10989
View this thread: http://www.excelforum.com/showthread...hreadid=394969

--

Dave Peterson