Solved

countifs

Posted on 2014-01-10
5
258 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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

789 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