# Simple formula

#### The Idea Dude

##### Well-known Member
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

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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

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

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))

Try:

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

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
ABCDEFG
1bluebluebluegreenwhiteblueblue
2
3
4hellodoghellohellocathelloball
5
6
Sumary

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

Oops!

Try:

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

WORKED LIKE A CHARM!

Thanks for the great help

Replies
5
Views
398
Replies
5
Views
215
Replies
2
Views
147
Replies
1
Views
63
Replies
7
Views
247

1,203,030
Messages
6,053,130
Members
444,640
Latest member
Dramonzo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back