Solved

countifs

Posted on 2014-01-10
5
257 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
  • 2
  • 2
5 Comments
 
LVL 6

Assisted Solution

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PowerPoint Links 7 44
iPhone excel activation issues 11 92
Extract Windows 10 data files from Parallels 12 Windows 10.pvm 14 78
Using Third Party DLL with Access VBA 14 17
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

821 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