Simple formula

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585
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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Sir Vili

Board Regular
Joined
Jul 11, 2002
Messages
148
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

Well-known Member
Joined
Aug 15, 2002
Messages
585
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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Try:

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

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,552
Members
417,151
Latest member
ChickenTenderer

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top