# COUNTIF function

1. ## COUNTIF function

Hi,

I have a worksheet which i want to use a countif function (i think!?) where i can look at 2 different columns of data and only count if in these columns a particular word is said. e.g.

column A has a list of region numbers and column I has an assessment result. I just want to do a count so that if column A has the number 102 in it and Column I has the word PASS in it then count this as 1.

this is what I am using at the moment, although this just counts the total of both:

=COUNTIF('Retirement Options - Combined'!A2:A599,"*101*")+COUNTIF('Retirement Options - Combined'!I2:I599,"*PASS*")

this is probably really easy but am struggling somewhat here.

many thanks

Ashley

2. Try SUMPRODUCT for multiple criteria, i.e. if you want to find rows where the entire contents of column A are 101 and column I "Pass"

=SUMPRODUCT(--('Retirement Options - Combined'!A2:A599=101),--('Retirement Options - Combined'!I2:I599=*PASS*))

[assumes 101 is numeric not text, otherwise put quotes around "101"]

or if 101 and pass are in amongst other text

=SUMPRODUCT(--ISNUMBER(SEARCH("101",'Retirement Options - Combined'!A2:A599)),--ISNUMBER(SEARCH("Pass",'Retirement Options - Combined'!I2:I599)))

3. Great Stuff, Thats Worked, Many Thanks!

4. Ashley,

You can also use an Array Formula (also called CSE or CTRL-SHIFT-ENTER Formulas).

For your example you can use:

=COUNT(IF('Retirement Options - Combined'!A2:A599=101,IF('Retirement Options - Combined'!I2:I599="pass",'Retirement Options - Combined'!A2:A599)))

The trick is NOT to click on the Green Check to run the formula (you will get 0), but, with the cursor at the end of the last close paren, press CTRL-SHIFT-ENTER at the same time. When you do that, it will put curly braces {} around the formula, and give you the correct result.

You might do some research on Array Formulas to get an idea of how they can do some pretty miraculous things...

Sincerely,

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1