Getting Counts

[Moz]

I am looking for the easiest way to count the number of items that meet a specific criteria in a spread sheet. I am using sumproduct to calculate the totals of the data and now I need to get a count of that data so I can calculate averages.

 

[Norman Harker]

Hi Moz!

If you are already using SUMPRODUCT you can use the following
structure:

=SUMPRODUCT(--(A1:A59="John"),--(B1:B59="Expenses"),(C1:C59))/SUMPRODUCT(--(A1:A59="John"),--(B1:B59="Expenses"))

The second SUMPRODUCT is calculating the count using the same criteria
used top calculate the total.

 

[JE McGimpsey]

So what's your "a specific critera"??

For a single criterion, you can use countif()

=COUNTIF(A1:A1000, ">0")

for multiple criteria, you may be able to use SUMPRODUCT:

=SUMPRODUCT(--(A1:A1000>0),--(B1:B1000="hello"))

 

[Moz]

Here is one of my sumproduct formulas

=SUMPRODUCT((Month_rng=SelectMonth)*(Adj_rng<>"A")*(Model_rng="UsdLtTrk"),Gross_rng)

This gives me the total gross of Used Light Truck with out Adjustments. Now I want a count of the Used Lt Truck

 

[JE McGimpsey]

To count UsdLtTrk:

=COUNTIF(Model_rng,"UsdLtTrk")

 

[JE McGimpsey]

or, if you want to only count UsdLtTrk in SelectMonth for which
Adj_rnage = "A":


=SUMPRODUCT(--(Month_rng=SelectMonth), --(Adj_rng<>"A"),
--(Model_rng="UsdLtTrk"))

 

[Norman Harker]

Hi Moz!

=COUNTIF(Model_rng,"UsdLtTrk")

Will return all cases of UsdLtTrk.

However, you mentioned you wanted to calculate the averake from your
SUMPRODUCT and this formula will count UsdLtTrk for cases where the
other other multiple conditions have resulted in their exclusion.

 

[David]

Norman,
use of comma (,) and asterisk (*) seem to be interchangable with SUMPRODUCT formula?
tia

 

[Norman Harker]

Hi David!

Not really! For counting problems there's no great problem with
substitution of the comma by the multiplication although there is
apparently a speed difference that is unlikely to be significant in
most applications.

For summing problems, you can hit difficulties. These arise if the
formula is used for summing a range that could contain boolean or text
entries.

=SUMPRODUCT(--(A103:A110="John"),--(B103:B110="Salary"),C103:C110)
Returns same as
=SUMPRODUCT((A103:A110="John")*(B103:B110="Salary"),C103:C110)

But:
=SUMPRODUCT((A103:A110="John")*(B103:B110="Salary")*C103:C110)
Returns #VALUE if the range C103:C110 contains text or Booleans.

 

[David]

Thanks Norman,
Very helpful (as usual)
--
David

Hi David!

Not really! For counting problems there's no great problem with
substitution of the comma by the multiplication although there is
apparently a speed difference that is unlikely to be significant in
most applications.

For summing problems, you can hit difficulties. These arise if the
formula is used for summing a range that could contain boolean or text
entries.

=SUMPRODUCT(--(A103:A110="John"),--(B103:B110="Salary"),C103:C110)
Returns same as
=SUMPRODUCT((A103:A110="John")*(B103:B110="Salary"),C103:C110)

But:
=SUMPRODUCT((A103:A110="John")*(B103:B110="Salary")*C103:C110)
Returns #VALUE if the range C103:C110 contains text or Booleans.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

 

[Norman Harker]

Hi David!

No problem. Never hesitate to ask for explanations. I think I got that
explanation from Harlan Grove in a discussion on the subject a couple
of months ago.

 

[David]

Norman,
Just one more question on the same theme...
=sumproduct(--(rng1="a"),--(rng2="B"),(--(rng4="c2)--(rng4="d")))
The OR criteria in the 3rd argument works ok but is this the right way to write it?

 

[Norman Harker]

Hi David!

I'm tempted to use the, "If it works don't mess with it!" approach.

But:
=SUMPRODUCT(--(rng1="a"),--(rng2="B"),(rng4="c2")+(rng4="d"))

The + used to get the "or" is serving to do the coercing of the
Boolean returns to 1 or 0.

 

[David]

Once again Norman,
Thankyou