Counting entries that satisfy multiple conditions

[Ben Karlin]

Like so many others that have posted in the last two days,
my question is about counting and conditions. I've read
the responses and read the help files but am not able to
understand enough to apply the info to my problem.

The entries in B2:B133 will be either P, AP, UP, or RP.
What I want to do is check the entries in J2:J133, count
those that are greater than 0, and return the results of
how many are P in one cell, AP, UP and RP in other cells.

I've been blown away by how quickly and cleverly answers
come and am very hopeful that there's really nothing to
doing this. Would it be possible to get a brief tutorial
or explanation along with an answer?

Thanks so much.

Ben Karlin
St. Louis, MO

 

[Norman Harker]

Hi Ben!

Here's one that does for Col B is P where Col J >0

=SUMPRODUCT(($J$2:$J$133>0)*($B$2:$B$133="P"))

Now if you were to place P, AP, UP and RP in a range X1:X4 then:

Z1:
=SUMPRODUCT(($J$2:$J$133>0)*($B$2:$B$133=X1))
Copy down to Z4

--
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.

 

[RagDyer]

You could enter these 4 formulas to return the counts together with the
labels of the counts:

="P = "&SUMPRODUCT((B2:B133="P")*(J2:J133>0))
="AP = "&SUMPRODUCT((B2:B133="AP")*(J2:J133>0))
="UP = "&SUMPRODUCT((B2:B133="UP")*(J2:J133>0))
="RP = "&SUMPRODUCT((B2:B133="RP")*(J2:J133>0))

Of course, you couldn't use these for further calculation.

If you do need to calculate these, simply delete everything in front of
SumProduct except , of course, the first = sign.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Like so many others that have posted in the last two days,
my question is about counting and conditions. I've read
the responses and read the help files but am not able to
understand enough to apply the info to my problem.

The entries in B2:B133 will be either P, AP, UP, or RP.
What I want to do is check the entries in J2:J133, count
those that are greater than 0, and return the results of
how many are P in one cell, AP, UP and RP in other cells.

I've been blown away by how quickly and cleverly answers
come and am very hopeful that there's really nothing to
doing this. Would it be possible to get a brief tutorial
or explanation along with an answer?

Thanks so much.

Ben Karlin
St. Louis, MO