[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
5
Medium Priority
?
266 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 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

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

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

649 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