Countif? or IF statement??

[paankadu]

I would like a formula that will look at column B and look for dept 1234 and
then look at column D to see how many of them have an X in that column and
give me the resulting number.

I am looking to do the same with the dept # and blanks in column D and then
also with dept# and M in column D.

We have about 25 different dept. numbers and 300+ people so am trying to
automate the attend (x) and made up (M) calculations for each dept.


B C D E
Dept Jan Feb
1234 X
4567
1234 X
8921 M
1234
1234 M

Any help will be greatly appreciated!

Thanks,
Angie

 

[Luke M]

=SUMPRODUCT(($B$2:$B$100=1234)*($D$2:$D$100="X"))

Note that if your departments are entered as text, you'll need to put the
1234 in quotes. As you may surmise, you could replace the '1234' with a cell
reference. For your other examples, simply change the "X" to "" or "M"

 

[T. Valko]

Try these:

=SUMPRODUCT(--(B2:B10=1234),--(D210="X"))

=SUMPRODUCT(--(B2:B10=1234),--(D210=""))

=SUMPRODUCT(--(B2:B10=1234),--(D210="M"))