Formula to COUNT certain criteria
 

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

Formula to COUNT certain criteria
 

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

DAVIS wrote:
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

Formula to COUNT certain criteria
 

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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Formula to COUNT certain criteria
 

THANK YOU NORMAN & FRANK!!!

-----Original Message-----
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

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.


.

Formula to COUNT certain criteria
 

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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.