Simple formula

[The Idea Dude]

Hi everyone,

I know this is a simple formula, but my brain just isn't working!!!

I need to count the number of times that, say, D1="hello" when A1="Blue"

What is the syntax?

Thanks

 

[Sir Vili]

Hello The Idea Dude.

If I understand correcty Your problem; then make formula in D1 supposing that in A1 there is text BLUE:

=IF(A1="BLUE","Hello","").

Best regards Sir Vili
This message was edited by Sir Vili on 2002-09-06 02:01

 

[The Idea Dude]

Not quite!

I will re word the question to make it a bit clearer.
The formula I am looking for is something like this (I just can't figure out what) - You should be able to see what I am trying to do from my Flawed formula:

=countif(and(A4:L4,"Hello", A1:L1,"Blue")

So, count the number of times that a cell in row 4 = "hello" AND the corresponding cell in row 1 = "Blue".

Thanks

 

[Mudface]

You will need column headers for this in cells A1 and D1. In the cell where you require the answer copy and paste in the following formula and then press Ctrl-Shift-Enter to put it in as an array formula (if you've entered it correctly it will appear surrounded by curly brackets {}). Adjust the 65536 row nos to the end of your range: -

=SUM(IF($A2:$A65536="blue",IF($D2:$D65536="hello",1,0),0))

 

[Andrew Poulsom]

Try:

=COUNTIF(A1:L1,"Blue")-COUNTIF(A4:L4,"<>Hello")

 

[The Idea Dude]

Hi, the suggestions have not worked exactly, perhaps this will further explain.

In the example below, the formula that I am chasing will give me an answer of 3. This is because there is 3 instances that in a given column, row 4 = "hello", and row 1 = "Blue". For the remaining columns, at least 1 condition is not true, therefore I don't need it counted. Thanks for your persistence, it is great to have access to such a knowledge base!!!!

DATOY Survey Results.xls
	A	B	C	D	E	F	G
1	blue	blue	blue	green	white	blue	blue
2
3
4	hello	dog	hello	hello	cat	hello	ball
5
6
Sumary

This message was edited by The Idea Dude on 2002-09-06 02:55

 

[Andrew Poulsom]

Oops!

Try:

=SUMPRODUCT((A1:G1="Blue")*(A4:G4="Hello"))

 

[The Idea Dude]

WORKED LIKE A CHARM!

Thanks for the great help