Adding multiple COUNTIFS issue

**purdue7997** · 04-17-2013, 06:03 AM

I'm trying to build a schedule and I'm running into problems. On rows 37 (and eventually 38 once I nail this formula down) I am attempting to calculate the total number of D shifts and D* shifts based on the duty position in column B. Both D and D* should only count if the duty shift is a AN, AFN, LPN, CIV LPN, or CIV RN. I have what I thought was a solid formula when I only enter D*. This gives me the correct total. However, once I enter just a D, the number adds two more than needed.

Where am I going wrong in formula in row 37? Please teach me to fish.

Please Login or Register  to view this content.

Thanks in advance!

**benishiryo** · 04-17-2013, 06:17 AM

hi purdue7997. you should probably give us a bit more data & tell us which cell is wrong, why so & what should be the right answer. it seems like you already know that you should use "D*", but also included "D"? "D*" will count both cells E13:E14. "D" will count E13. hence the double counting & resulting in 3. and if you know how to use the curly brackets, you can do this in E37:
=SUM(COUNTIFS($B$6:$B$35,{"AN","AFN","LPN","CIV LPN","CIV RN"},E6:E35,"D*"))

and since you want to copy it down, you should also fix E6:E35 & include column C as a criteria:
=SUM(COUNTIFS($B$6:$B$35,{"AN","AFN","LPN","CIV LPN","CIV RN"},E$6:E$35,"D*",$C$6:$C$35,LEFT($C37)))

**mnjofra** · 04-17-2013, 06:26 AM

Hi,
I put solution in 40th row of your file. It is formula (for column E):

Please Login or Register  to view this content.

Note, that this formula is without array notification, because of using SUMPRODUCT function.

Regards,

**purdue7997** · 04-17-2013, 06:28 AM

That code is much shorter and manageable than I originally came up with. It works for what I'm after. Thanks a bunch! I tried giving you rep but it seems I gave you some last time and need to spread more to others first. Thanks!

**Portuga** · 04-17-2013, 06:40 AM

Glad you found a solution.

This is just an explanation of why it was doing it.

The issue relates to the fact that you are considering "D*" in itself as a unique value, while used in a formula like that, works as a wildcard, counting all cells with a D on it. So if you put "DCN", it will count that also, since it has a D on it.

In countifs for this to work, you would have to change it to "D~*"