Countif? or IF statement??
 

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

Countif? or IF statement??
 

=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"
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"paankadu" wrote:

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

Countif? or IF statement??
 

Try these:

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

=SUMPRODUCT(--(B2:B10=1234),--(D2:D10=""))

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

--
Biff
Microsoft Excel MVP


"paankadu" wrote in message
...
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