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!
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?
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?
=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?
If you haven't tried before, you may want to look at the database functions in Excel. In particular, Dcount or Dcounta.
http://www.ce.memphis.edu/1112/excel/dcount.htm
RE: COUNTIF with multiple conditions?
------------------------------------------
"...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?
{=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?
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?
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?
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?
------------------------------------------
"...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?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: COUNTIF with multiple conditions?
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?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: COUNTIF with multiple conditions?
http://www.mrexcel.com/tip031.shtml