Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Novice IF and COUNT question (I think)
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Novice IF and COUNT question (I think)
=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 "Robin" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Novice IF and COUNT question (I think)
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 wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Novice IF and COUNT question (I think)
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" wrote: =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 "Robin" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Novice IF and COUNT question (I think)
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
-- Regards, Peo Sjoblom "Robin" wrote in message ... 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" wrote: =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 "Robin" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Novice IF and COUNT question (I think)
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 On Oct 3, 6:47 pm, Robin wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel novice question on filling in missing fields/cells | Excel Discussion (Misc queries) | |||
Very Novice Excel user with security question | Excel Discussion (Misc queries) | |||
Excel Novice, Stupid Question, but help! | Excel Discussion (Misc queries) | |||
Novice needs help. | Excel Discussion (Misc queries) | |||
drop down list - a question from novice | Excel Discussion (Misc queries) |