Formula to COUNT certain criteria

  • Thread starter Thread starter DAVIS
  • Start date Start date
D

DAVIS

Hello,

We are hoping to set up a formula to effectively counting
weekly data (instead of manually counting them). We
usually have a set of raw data coming from a database and
wants to identify have many of them are >=100, >=50, <=10,
and etc... Base on the code (HGR, NEW).

Is there a way that I can look up a particular code (NEW)
first and see how many of data that are >=100 within this
NEW code. In the example below, the count would be "2"
because 2 of them have >=100 submissions.


Example:
Code Submissions
HGR 105
HGR 100
HGR 58
NEW 10
NEW 5
NEW 63
NEW 105
NEW 110

Thank you in advance!!!
Davis
 
Hi Davis,

assuming that your code is in column A and the submissions in column B
you can use SUMPRODUCT. For your example enter the following formula:
=SUMPRODUCT((A2:A999="NEW")*(B2:B999>=100))

HTH
Frank
 
Hi Davis!

Try:
=SUMPRODUCT(($A$1:$A$34="NEW")*($B$1:$B$34>=100))

Or put codes in a row starting (eg) at E15 and criteria in a column
starting at (eg) D16

E16:
=SUMPRODUCT(($A$1:$A$34=E$15)*($B$1:$B$34>=$D16))
Copy down and across.

But you might like to try using a pivot table.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Davis!

Thanks are always appreciated. When you've cleared your immediate
problem, take a look at the power of Pivot Tables.

See:
http://peltiertech.com/Excel/Pivots/pivotstart.htm
A really good introduction to Pivot tables by Debra Dalgleish.

And keep posting.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top