General discussion


Testing value of 2 cells in COUNTIF()

By raymond.hale ·
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by DKlippert In reply to Testing value of 2 cells ...

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:

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

Collapse -

by IT_Cowboy In reply to Testing value of 2 cells ...

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

Related Discussions

Related Forums