Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF(AND Statement | Excel Discussion (Misc queries) | |||
countif statement | Excel Worksheet Functions | |||
If Statement with Countif | Excel Discussion (Misc queries) | |||
Countif statement | Excel Worksheet Functions | |||
countif statement | Excel Worksheet Functions |