Counting Multiple Text Cells

[Aaron G]

I am trying to determine the formula to count - only if the criteria in two
(or more) cells is true.

Example Data Set

Service Equipment
Data ATM
Voice DMS 250
Voice DMS 300
Data FR
Voice DMS 250

So i am looking for something to show how often both the serivce and the
equipment criteria meet the desired output.

I tried writing a formula like =COUNTIF(A2:A6,"Voice")AND(B2:B6,"DMS250")

where it would then count the data set and report the value of 2 ... 2 times
the criteria is both voice and dms250.

Any thoughts???

Thanks

 

[T. Valko]

Try this...

=SUMPRODUCT(--(A2:A6="Voice"),--(B2:B6="DMS250"))

Better to use cells to hold the criteria:

D2 = voice
E2 = DMS250

=SUMPRODUCT(--(A2:A6=D2),--(B2:B6=E2))

If you're using Excel 2007:

=COUNTIFS(A2:A6,D2,B2:B6,E2)

 

[Mike H]

Hi

=SUMPRODUCT((A1:A10="Voice")*(B1:B10="DMS250"))

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Gord Dibben]

=SUMPRODUCT((A2:A6="Voice")*(B2:B6="DMS 250"))


Gord Dibben MS Excel MVP

On Fri, 22 Jan 2010 13:09:01 -0800, Aaron G <Aaron

 

[Ashish Mathur]

Hi,

Suppose data is in range A1:B6 (row 1 has the header row)

In A9:B9, enter Service and Equipment. In A10:B10, enter Voice and DMS 250.
In C9, enter Equipment and in C10, enter =DCOUNTA(A1:B6,C8,A8:B9)

If you wish to copy and paste this formula down as well for more criteria in
A11:B11 and below, you may use the following formula in cell C10 and then
paste down

=DCOUNTA($A$1:$B$6,C$8,$A$8:B9)-SUM(C$8:C8)

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com