COUNTIF with multiple conditions?

[ACtrafficengr]

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

 

[prost]

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.

 

[ACtrafficengr]

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

 

[EGT01]

ACtrafficengr,

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

 

[ACtrafficengr]

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

 

[bltseattle]

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.

 

[EGT01]

ACtrafficengr,

Here's a simple example of what I think you want to do using DCOUNT...
[tt]
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
[/tt]
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?

 

[electricpete]

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.

 

[onlyadrafter]

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!

 

[ACtrafficengr]

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

 

[electricpete]

onlyadrafter - what does the -- symbol do?

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

 

[onlyadrafter]

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!

 

[electricpete]

oad - thanks.

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

 

[awomack]

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