CODEWITHSUNDEEP
exceloffset
sherrellbc
sherrellbc

Reputation: 4853

Excel - Determine if several criteria are met

I have a column of 1241 values ranging from 1 - 3. What I need to do, for example, is the following:

Check if the cell is equal to 3. If the check is true, check if the associated value 18 columns to the left is equal(OR CONTAINS!) a specific string. I thought something like this:

=COUNTIF(S2:S1242,  OFFSET(S2:S1242, 0, -18) = "StringToCheck")

But it did not work. I know how to do this if I were to take a column and check each row individually and then sum the 1241 values, but there has to be a simpler way.

Any suggestions?

Upvotes: 0

Views: 1110

Answers (1)

Andy G
Andy G

Reputation: 19367

Assuming you want to count how many rows meet both criteria, and that you are using Excel 2007 or above:

=COUNTIFS(A1:A7,3,D1:D7,"Hello")

If you are using Excel 2003 or below then it requires an array formula:

=SUM(IF(A1:A7=3,IF(D1:D7="Hello",1,0),0))

use Ctrl-Shift-Enter (rather then just Enter) to complete the formula. This signifies that it is an array-formula.

Upvotes: 3

Related Questions