×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

COUNTIF with multiple conditions?

COUNTIF with multiple conditions?

COUNTIF with multiple conditions?

(OP)
I suppose I could use Access to do this, but I'm still at the bottom of that particular learning curve.

Is there any way to count cells based on two or more criteria, say for example, count if year=2005 and priority=high?

Thanks!

------------------------------------------
     "...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail."

              Wm. Phelps Eno, ca. 1928

RE: COUNTIF with multiple conditions?

Found this at
http://www.ozgrid.com/Excel/count-if.htm

Let's say we need to count all the cells in A1:A20 that are greater than 2, but less than 5. The COUNTIF example below will achieve this;

=COUNTIF($A$1:$A$20,">2")-COUNTIF($A$1:$A$20,"<5")

Looks like the trick then is to add up all the cells greater than 2, then subtract the count of all those cells that are less then 5.

RE: COUNTIF with multiple conditions?

(OP)
What I want to do is more like

=COUNTIF(($A$1:$A$20,">2") AND ($b$1:$b$20,"=C"))

From the link you gave me, I could nest IFs in a COUNT, or maybe it's time to learn pivot tables.

------------------------------------------
     "...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail."

              Wm. Phelps Eno, ca. 1928

RE: COUNTIF with multiple conditions?

(OP)
EG. I couldn't get them to work. Any more suggestions?

------------------------------------------
     "...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail."

              Wm. Phelps Eno, ca. 1928

RE: COUNTIF with multiple conditions?

Try this array formula.  The & symbol will concatenate the data in the two columns before comparing to your criteria.  The array formula will go row by row and check the condition. Because the resultant array will only contain a 1 where the condition was met, you can then sum the 1s to find the total times the condition was met.

{=SUM(IF(B5:B13&C5:C13="2004high",1,0))}

In case you don't know about array formulas, do not type the curly brackets; type the formula, then ctrl-shft-enter instead of just -enter-, at which point the curly brackets will appear that indicate it is an array formula.

RE: COUNTIF with multiple conditions?

ACtrafficengr,

Here's a simple example of what I think you want to do using DCOUNT...

Num    Letter
1.1    A
0.3    B
2.1    C
2.3    D
2.1    C
2.7    B
1.5    C
2.8    D
2.4    C
    
Num    Letter
>2     C
dc=    3

1.  Highlight and copy the above.
2.  Paste Special as Text into Excel at cell A1.  I think this will put it into 2 columns but use Data\Text to Columns if needed.
3.  The number 3 should be in cell B14.  Replace the value 3 with the equation =DCOUNT(A1:B10,A1,A12:B13)

Basically, what this does is counts the number of cells in the Num column when Num > 2 and Letter = C.

Does this help?

RE: COUNTIF with multiple conditions?

If you are wiling to create a new column, one solution would be

create a new column (let's say column H).

In H1 we have
IF((A1>2)*(b1="C"))
(* works as shorthand for AND)


Copy H1 formula down the column all the way to H20.

H21 is =sum(H1:H20)  will show the number of rows meeting the criteria.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: COUNTIF with multiple conditions?

Hello,

I think SUMPRODUCT would be better

=SUMPRODUCT(--($A$1:$A$4=D1),--($B$1:$B$4=E1))

where A1 - A4 are the years,
B1 - B4 is the high/low


D1 contains a year
E1 contains 'high', 'low' etc

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

RE: COUNTIF with multiple conditions?

(OP)
I've actually ahdsome success withthe conditional sum wizard add-in. It's a bit clunky, but it seems to work.

------------------------------------------
     "...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail."

              Wm. Phelps Eno, ca. 1928

RE: COUNTIF with multiple conditions?

onlyadrafter - what does the -- symbol do?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: COUNTIF with multiple conditions?

Hello,

Here is an explanation from Aladin Akyurek from the MrExcel Forum site

Expensiveness in ascending order for the oft-used coercers:

--

+0

*1

Plus Zero is closer to double Nots.

Note that all math operations cause coercion lke in

=TRUE^10

Recall also that the syntax for SumProduct is...

SumProduct(X1,X2,...)

where each X must be either a numerical range or a numerical array object.

Given the above syntax,

=SUMPRODUCT(A2:A10,B2:B10)

is faster than

{=SUM(A2:A10*B2:B10)}

but also faster than

=SUMPRODUCT(A2:A10*B2:B10)

where both A2:A10and B2:B10are numerical range objects.

In multiconditional computations (say multiconditional counting), we have often logical array objects to consider, e.g.,

E2:E10="UP" and F2:F10=2003

Lets name such expressions conditionals.

[1]

{=SUM((E2:E10="UP")*(F2:F10=2003))}

would give the desired multiconditional count as would:

[2]

=SUMPRODUCT((E2:E10="UP")*(F2:F10=2003))

[3]

=SUMPRODUCT(--(E2:E10="UP"),--(F2:F10=2003))

[4]

=SUMPRODUCT((E2:E10="UP")+0,(F2:F10=2003)+0)

[5]

=SUMPRODUCT((E2:E10="UP")*1,(F2:F10=2003)*1)

In [1] and [2], the coercion occurs as a side-effect of a mathematical operation (a multiplication as indicated by *).

In [3] to [5], we first coercethe arrays of logical values as result of the conditional evaluations into numerical arrays (as SumProduct needs/expects them), which then get multiplied by SumProduct (the comma in the syntax of SumProduct stands for "vector multiplication") then summed.

The timing results show that coercion by -- leads to less jumpy computations. That is, each run shows the same timing characteristics, compared to a coercion with +0 (coercion with *1 is slower). Average time are almost the same for SumProduct formulas that use these coercers.

The SumProduct formulas would be much faster if SumProduct would implicitly coerce (that is, without the need for an explicit coercer like -- or +0) the results of conditional evaluations into numbers.

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

RE: COUNTIF with multiple conditions?

oad - thanks.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: COUNTIF with multiple conditions?

This is the explanation of the array formula I used for the same type of multiple criteria counting.  Worked really well for me.

http://www.mrexcel.com/tip031.shtml

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources