[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# countifs

Posted on 2014-01-10
Medium Priority
266 Views
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
0
Question by:jvantassel1
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 6

Assisted Solution

Sasha Kranjac earned 800 total points
ID: 39772683
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

LVL 81

Accepted Solution

byundt earned 1200 total points
ID: 39772939
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

LVL 6

Expert Comment

ID: 39774102
@byundt
This is much more elegant formula - I like using an array, especially if there are more values to add.
0

LVL 1

Author Comment

ID: 39776823
I should get a chance to look at this later today or tomorrow.
0

LVL 1

Author Closing Comment

ID: 39777692
Both solutions work.  I agree the second solution is more elegant.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question