Count if

[venkateswaran.v]

All,
I've 2 columns. One is a Priority having values (L,M,H) & the other is
a Status (Passed,Failed). There a many rows with different
combinations of the above. How do i count how many "L" are in
"Passed"? How Many "H" are in failed etc.
I dont want to add one more column to concatenate these 2 like
"LPassed", "HFailed" etc..
Is there are formula to count these without concatenating them. Assume
i have data for 100 rows.

Anyhelp would be highly appreciated

 

[Pete_UK]

COUNTIF only works with one variable. If you do not want to
concatenate your variables into one helper column, you could use this:

=SUMPRODUCT((A$1:A$100="L")*(B$1:B$100="Passed"))

Alternatively, you could put the values you are interested in counting
in two cells (eg D1 and E1) and then the formula becomes:

=SUMPRODUCT((A$1:A$100=D1)*(B$1:B$100=E1))

(perhaps in F1). You can easily change the values in D1 or E1 and get
other counts without having to change the formula. You might like to
put all variations in D1:E6 and then copy the formula down to get a
small table of results.

Hope this helps.

Pete