Novice IF and COUNT question (I think)

[Robin]

I know there must be a worksheet function to do what I am doing by various
degrees of filtering and sorting, so I thought I'd ask here. Basically:

Column A contains data on work location, eg, IHR, CPD, ED
Column B contains data on whether or not someone passed or failed a test, eg
P or F

I need to know, how many people from each work area passed and failed a
test. For example, if data in Column A = IHR, how many people (in that work
area) passed the test and how many failed the test

Currently, Im sorting the data to get all my IHRs grouped together, then
adding blank rows below each work location and counting the # of Ps and Fs
for that "group". I know there must be an easier way. Assistance is much
appreciated.
Thanx.

 

[Peo Sjoblom]

=SUMPRODUCT(--(A2:A200="IHR"),--(B2:B200="P"))

will count IHR that passed, it's easier to use if you change it to

=SUMPRODUCT(--(A2:A200=C2),--(B2:B200=D2))

where C2 holds the work locations and D2 the passed/failed abbreviation

adapt to fit the your real range sizes etc


--


Regards,


Peo Sjoblom

 

[JW]

Few different ways. You could use CountIf formulas, pivot table, or
subtotals. I would probably use subtotals personally. Sort your data
by column A and then by column B. Choose Data-->Subtotals. Make the
necessary adjustments to the criteria and click OK. You can also have
multilevel subtotals (what I would do) by doing the subtotal process
over again and checking off the Replace Current Subtotals checkbox.

All that said, I would:
1-Sort on column A the column B.
2-Subtotal to count the total number of tests taken per work location.
3-Second subtotal to count the occurrences of P or F per work
location.

 

[Robin]

Fantastic! THANK YOU. This one worked just fine:

=SUMPRODUCT(--(A2:A200="IHR"),--(B2:B200="P"))

I will have to learn more about the SUMPRODUCT function. What purpose do
the dashes serve?

Regarding the following suggestion:
it's easier to use if you change it to

=SUMPRODUCT(--(A2:A200=C2),--(B2:B200=D2))

where C2 holds the work locations and D2 the passed/failed abbreviation

If C holds work locations and D the P/F abbreviation, what is in columns A
and B?

 

[Peo Sjoblom]

http://www.mcgimpsey.com/excel/formulae/doubleneg.html


--


Regards,


Peo Sjoblom

 

[Pete_UK]

Note that Peo said:

... C2 holds the work locations and D2 the passed/failed abbreviation

not column C and column D. He meant for you to put "IHR" in C2 and "P"
in D2 and the amended formula will work as before. The advantage of
this is that you can easily change what is in those two cells and get
a different result, without having to modify the formula.

Hope this helps.

Pete