countifs

The attached spreadsheet uses the COUNTIFS function to determine how many briefs have been completed (cell J3) based on the value of the drop box located in Column F (status), now, instead of counting the brief complete only if the Status is "Packet Complete" I want it to count it if it's equal to "PB*" as well.  I tried using an OR() statement:   =COUNTIFS(pegName,IIpeg,MDEP_Briefstatus,OR("Packet Complete","PB Mission Complete")) unfortunately, when I do this the value is 0.
POM-1620-Murder-Board-Dates-and-.xlsm
LVL 1
jvantassel1Asked:
Who is Participating?
 
byundtCommented:
You can shorten the formula by using an array constant in the COUNTIFS. The curly braces { } mean that the contents are an array constant, to be plugged into the COUNTIFS one at a time. Wrapping the COUNTIFS in a SUM function then adds up the values, making the result equivalent to an OR.
=SUM(COUNTIFS(pegName,IIpeg,MDEP_Briefstatus,{"Packet Complete","PB*"}))
0
 
Sasha KranjacCommented:
Initially, in J4, calculated value is 32. I have entered the following formula:
=COUNTIFS(pegName;IIpeg;MDEP_Briefstatus;PacketComplete)+COUNTIFS(pegName;IIpeg;MDEP_Briefstatus;"PB*")
and the result was 36. It seems ok as there are only 4 cells that match "II" in Column A and "PB*" in Column F.
Replace semicolons in formula with commas if you get an error.
Would that work?
0
 
Sasha KranjacCommented:
@byundt
This is much more elegant formula - I like using an array, especially if there are more values to add.
0
 
jvantassel1Author Commented:
I should get a chance to look at this later today or tomorrow.
0
 
jvantassel1Author Commented:
Both solutions work.  I agree the second solution is more elegant.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.