Getting Counts

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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"))
 
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
 
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"))
 
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.
 
Norman,
use of comma (,) and asterisk (*) seem to be interchangable with SUMPRODUCT formula?
tia
 
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.
 
Thanks Norman,
Very helpful (as usual)
--
David


Norman Harker said:
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)
Click to expand...
 
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.
 
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?
 
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.
 
Back
Top