Solved

countifs

Posted on 2014-01-10
5
261 Views
Last Modified: 2014-01-13
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
Comment
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
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 6

Assisted Solution

by:Sasha Kranjac
Sasha Kranjac earned 200 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

by:
byundt earned 300 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

by:Sasha Kranjac
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

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

Author Closing Comment

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

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question