"countif" and "and" functions

[PamO]

Is it possible to use a "countif" function with an "and"
function which will give the numeric value of cells in
different columns, but the same rows, that meet the
criteria?

 

[Max]

One way is to use SUMPRODUCT instead

Example:

In E1: =SUMPRODUCT((A1:A4="a")*(B1:B4="c"))

will count the number of instances in which:

"a" occurs in A1:A4
*and*
"c" occurs in B1:B4

Note that the ranges A1:A4, B1:B4 have to be similar
and you can't use entire col refs (A:A, B:B) in SUMPRODUCT

The formula can be expanded to cover > 2 criteria, e.g.:
=SUMPRODUCT((A1:A4="a")*(B1:B4="c")*(C1:C4="d"))

 

[Max]

"a" occurs in A1:A4

*and*
"c" occurs in B1:B4

Think the above might be better phrased as:
"a" and "c" occuring in the same row