COUNTIF and numerous columns

[the_gnome]

I have the following columns

A=month
B=year
C=Priority
D=Inspected


Column A data is Nov, Dec etc
Column B data is 2003, 2004 etc
Column C data is A, B1, B2, B3, B4
Column D data is AA or MW or Blank

I would like to Count the number of NONBLANK cells in column D when column
A=Nov and column B=2003 and column C=A


Can this be done, and if so would some kind soul please provide the formula.

many thanks in advance

alan armstrong

 

[Peo Sjoblom]

This assumes that the Nov is text and not formatted date

=SUMPRODUCT((A2:A20="Nov")*(B2:B20=2003)*(C2:C20="A")*(D220<>""))

if column A are dates formatted as mmm you can use

=SUMPRODUCT((MONTH(A2:A20)=11)*(B2:B20=2003)*(C2:C20="A")*(D220<>""))

 

[the_gnome]

I am not doubting your skill but it looks like the formula would multiply
the total NOVs with Total 2003s with the total As with the total blanks.

This is assuming * means multiply.

Or am I missing something ?

Alan

 

[Peo Sjoblom]

Here's a little tutorial

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

 

[the_gnome]

Thanks for that, I think I need to do a degree course in excel, my brain
cannot accept such complexity.

Alan Armstrong