Software·43,768 discussions

Testing value of 2 cells in COUNTIF()

Locked

Is there a way to test for values in two different cells of a row when using COUNTIF() in a column? In other words,something like COUNTIF ((b3=”x”) AND (b4=3)). I would also like to know how to test a cell for the color fill pattern and count it if it is a certain fill color.

Topic

Reply To: Testing value of 2 cells in COUNTIF()

In reply to Testing value of 2 cells in COUNTIF()

The layout of your first question is a little vague. COUNTIF does not handle Boolean operators, but you could count all the x’s and then add the count for 3’s.
If you’re testing for the co-existence of two values, you may want an array formula.
Chip Pearson has covered this quite well at:

http://www.cpearson.com/excel/array.htm

He also discusses count by color, which requires a tiny bit of VBA:

http://www.cpearson.com/excel/colors.htm

Reply To: Testing value of 2 cells in COUNTIF()

In reply to Testing value of 2 cells in COUNTIF()

You can concatenate the two values then look for the value they create when they are joined together with the COUNTIF function.

For Example: In B5, create a formula “=B3&B4” which will produce “X3”. Then create COUNTIF(“Row 5 range”,”X3″).

[Author]

Replies