COUNTIF isn't counting one of my criteria

[AnnasBananas]

I think I've jumped into the part of Excel where I can't swim. Thanks in advance for the group's help!!

I'm counting multiple criteria: is it "completed", with this person "cell location", and site includes these letters at the beginning "

=COUNTIFS($B40:$B1033,"Completed",$F40:$F1033,W4,$D40:$D1033,{"PT*";"CF*";"IV*";"DS*";"SR*"})

I have reviewed the data and discovered that it's not counting any of my "CF*" data. Any ideas why it would leave that out of the count?

 

[Joe4]

I have reviewed the data and discovered that it's not counting any of my "CF*" data. Any ideas why it would leave that out of the count?

Welcome to the Board!

Check the "CF" values. Is there a space in front of the "CF" (because as you have written it, it will only return entries where the "CF" occurs in the first two spaces.
Also verify that the "CF" entries meet the other two criteria.

I would start with this, and see if it returns anything:

=COUNTIFS($D40:$D1033,"CF*")

If it does, then try adding one of the other conditions in, and test that.
If that works, then try adding in the last condition.
Once that works, add the other values you are looking for in column D.

 

[DanteAmor]

Hi and welcome to MrExcel
Try this:

=SUM(COUNTIFS($B40:$B1033,"Completed",$F40:$F1033,W4,$D40:$D1033,{"PT*";"CF*";"IV*";"DS*";"SR*"}))

 

[AnnasBananas]

I would start with this, and see if it returns anything:

=COUNTIFS($D40:$D1033,"CF*")

If it does, then try adding one of the other conditions in, and test that.
If that works, then try adding in the last condition.
Once that works, add the other values you are looking for in column D.

Progress!...This exercise taught me that only the first of the criteria in the last section is being counted {"PT*";"CF*";"IV*".....}. Only the "PT*" is counted, not the other ones. That leads me to believe that I need to write the formula either with something other than semi-colons, or separate each criteria???

 

[AnnasBananas]

=SUM(COUNTIFS($B40:$B1033,"Completed",$F40:$F1033,W4,$D40:$D1033,{"PT*";"CF*";"IV*";"DS*";"SR*"}))

Thanks for the idea, Donte. Unfortunately, that seemed to return some number that is further from the expected number.

 

[Joe4]

Well, there is probably a better way, but the brute force method should work, regardless:
=COUNTIFS($B40:$B1033,"Completed",$F40:$F1033,W4,$D40:$D1033,"PT*")
+COUNTIFS($B40:$B1033,"Completed",$F40:$F1033,W4,$D40:$D1033,"CF*")
+COUNTIFS($B40:$B1033,"Completed",$F40:$F1033,W4,$D40:$D1033,"IV*")
+COUNTIFS($B40:$B1033,"Completed",$F40:$F1033,W4,$D40:$D1033,"DS*")
+COUNTIFS($B40:$B1033,"Completed",$F40:$F1033,W4,$D40:$D1033,"SR*")

 

[RoryA]

SUM is the simplest method. If it's not producing the number you expect, then your expectations need revising, or your data does.

 

[AnnasBananas]

Well, there is probably a better way, but the brute force method should work, regardless:
=COUNTIFS($B40:$B1033,"Completed",$F40:$F1033,W4,$D40:$D1033,"PT*")
+COUNTIFS($B40:$B1033,"Completed",$F40:$F1033,W4,$D40:$D1033,"CF*")
+COUNTIFS($B40:$B1033,"Completed",$F40:$F1033,W4,$D40:$D1033,"IV*")
+COUNTIFS($B40:$B1033,"Completed",$F40:$F1033,W4,$D40:$D1033,"DS*")
+COUNTIFS($B40:$B1033,"Completed",$F40:$F1033,W4,$D40:$D1033,"SR*")

Yes, I did get that to work. You're right; it's clunky, but functional. I found a link you once posted to a tutorial section on COUNTIFS and OR functions. I found I needed to wrap the whole thing with SUM(...). (Which is what Donte had suggested, but I put his formula into the wrong cell. Eeek!)

Whew! Thank you!

 

[AnnasBananas]

Thanks for the idea, Donte. Unfortunately, that seemed to return some number that is further from the expected number.

Donte, I tried it again because I realized I had put the formula you suggested into the wrong cell (hence the crazy number I got back). It worked!!

What an amazingly clever community! Thanks, Everyone!

 

[Joe4]

SUM is the simplest method. If it's not producing the number you expect, then your expectations need revising, or your data does

Rory is right. I just set up an example and tried Dante's equation, and it returns the expected value. So I don't see any reason why that shouldn't work.

EDIT: Just saw your last post. Glad you figured it out!