Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function within SUMIF
I'm working with a spreadsheet that in part looks at Trainee
retention/turnover. When a trainee leaves, I count it in the total for the area using a COUNTIF function. I also need to acquire a total for each of the two trainers in the area (note the trainees are not sorted by trainer so I don't think using SUBTOTAL would be an option here). An example would be as follows: Trainee Trainer Turnover/Retention Sample 1 Trainer A Y Sample 2 Trainer B N Sample 3 Trainer B Y Sample 4 Trainer A N Total Trainer A Total Trainer B Area Total I tried using the following formula to get the results, but Excel isn't liking it. I'm thinking I don't have the parentheses correct. =SUMIF(C5:C24, "Trainer A",{COUNTIF(I5:I24, "N")}) Anyone have any ideas/opinions on this? Thanks! -- Teri Albert |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function within SUMIF
On Apr 23, 2:11 pm, tralbert wrote:
I'm working with a spreadsheet that in part looks at Trainee retention/turnover. When a trainee leaves, I count it in the total for the area using a COUNTIF function. I also need to acquire a total for each of the two trainers in the area (note the trainees are not sorted by trainer so I don't think using SUBTOTAL would be an option here). An example would be as follows: Trainee Trainer Turnover/Retention Sample 1 Trainer A Y Sample 2 Trainer B N Sample 3 Trainer B Y Sample 4 Trainer A N Total Trainer A Total Trainer B Area Total I tried using the following formula to get the results, but Excel isn't liking it. I'm thinking I don't have the parentheses correct. =SUMIF(C5:C24, "Trainer A",{COUNTIF(I5:I24, "N")}) Anyone have any ideas/opinions on this? Thanks! -- Teri Albert Try this. =SUMPRODUCT((C5:C24="Trainer A")*(I5:I24="N")) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function within SUMIF
=SUMPRODUCT(--(C5:C24="Trainer A"),--(I5:I24="N"))
-- Regards, Peo Sjoblom "tralbert" wrote in message ... I'm working with a spreadsheet that in part looks at Trainee retention/turnover. When a trainee leaves, I count it in the total for the area using a COUNTIF function. I also need to acquire a total for each of the two trainers in the area (note the trainees are not sorted by trainer so I don't think using SUBTOTAL would be an option here). An example would be as follows: Trainee Trainer Turnover/Retention Sample 1 Trainer A Y Sample 2 Trainer B N Sample 3 Trainer B Y Sample 4 Trainer A N Total Trainer A Total Trainer B Area Total I tried using the following formula to get the results, but Excel isn't liking it. I'm thinking I don't have the parentheses correct. =SUMIF(C5:C24, "Trainer A",{COUNTIF(I5:I24, "N")}) Anyone have any ideas/opinions on this? Thanks! -- Teri Albert |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function within SUMIF
Try this:
=SUMPRODUCT(--(C5:C24="Trainer A"),--(I5:I24="N")) If I understand you correctly, then this will count the number of times "Trainer A" had "N" in column I. HTH, Paul -- "tralbert" wrote in message ... I'm working with a spreadsheet that in part looks at Trainee retention/turnover. When a trainee leaves, I count it in the total for the area using a COUNTIF function. I also need to acquire a total for each of the two trainers in the area (note the trainees are not sorted by trainer so I don't think using SUBTOTAL would be an option here). An example would be as follows: Trainee Trainer Turnover/Retention Sample 1 Trainer A Y Sample 2 Trainer B N Sample 3 Trainer B Y Sample 4 Trainer A N Total Trainer A Total Trainer B Area Total I tried using the following formula to get the results, but Excel isn't liking it. I'm thinking I don't have the parentheses correct. =SUMIF(C5:C24, "Trainer A",{COUNTIF(I5:I24, "N")}) Anyone have any ideas/opinions on this? Thanks! -- Teri Albert |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function within SUMIF
That did the trick! Thanks! Next question if you happen to know, I'm not
wanting to limit the range of the two columns to those specific rows (i.e. C5:C24). I know there is a wildcard entry I can input in so it will substitute a value in for each row, but I can't remember for the life of me what that wildcard value is. Am I making sense? Thanks again! -- Teri Albert "Spiky" wrote: On Apr 23, 2:11 pm, tralbert wrote: I'm working with a spreadsheet that in part looks at Trainee retention/turnover. When a trainee leaves, I count it in the total for the area using a COUNTIF function. I also need to acquire a total for each of the two trainers in the area (note the trainees are not sorted by trainer so I don't think using SUBTOTAL would be an option here). An example would be as follows: Trainee Trainer Turnover/Retention Sample 1 Trainer A Y Sample 2 Trainer B N Sample 3 Trainer B Y Sample 4 Trainer A N Total Trainer A Total Trainer B Area Total I tried using the following formula to get the results, but Excel isn't liking it. I'm thinking I don't have the parentheses correct. =SUMIF(C5:C24, "Trainer A",{COUNTIF(I5:I24, "N")}) Anyone have any ideas/opinions on this? Thanks! -- Teri Albert Try this. =SUMPRODUCT((C5:C24="Trainer A")*(I5:I24="N")) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function within SUMIF
With SUMPRODUCT you will have to give it a specific range. However, you can
make the range large enough to suit your needs. Example: =SUMPRODUCT((C5:C1000="Trainer A")*(I5:I1000="N")) or =SUMPRODUCT(--(C5:C1000="Trainer A"),--(I5:I1000="N")) HTH, Paul -- "tralbert" wrote in message ... That did the trick! Thanks! Next question if you happen to know, I'm not wanting to limit the range of the two columns to those specific rows (i.e. C5:C24). I know there is a wildcard entry I can input in so it will substitute a value in for each row, but I can't remember for the life of me what that wildcard value is. Am I making sense? Thanks again! -- Teri Albert "Spiky" wrote: On Apr 23, 2:11 pm, tralbert wrote: I'm working with a spreadsheet that in part looks at Trainee retention/turnover. When a trainee leaves, I count it in the total for the area using a COUNTIF function. I also need to acquire a total for each of the two trainers in the area (note the trainees are not sorted by trainer so I don't think using SUBTOTAL would be an option here). An example would be as follows: Trainee Trainer Turnover/Retention Sample 1 Trainer A Y Sample 2 Trainer B N Sample 3 Trainer B Y Sample 4 Trainer A N Total Trainer A Total Trainer B Area Total I tried using the following formula to get the results, but Excel isn't liking it. I'm thinking I don't have the parentheses correct. =SUMIF(C5:C24, "Trainer A",{COUNTIF(I5:I24, "N")}) Anyone have any ideas/opinions on this? Thanks! -- Teri Albert Try this. =SUMPRODUCT((C5:C24="Trainer A")*(I5:I24="N")) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function within SUMIF
Ok, that will work at least. Thanks!
-- Teri Albert "PCLIVE" wrote: With SUMPRODUCT you will have to give it a specific range. However, you can make the range large enough to suit your needs. Example: =SUMPRODUCT((C5:C1000="Trainer A")*(I5:I1000="N")) or =SUMPRODUCT(--(C5:C1000="Trainer A"),--(I5:I1000="N")) HTH, Paul -- "tralbert" wrote in message ... That did the trick! Thanks! Next question if you happen to know, I'm not wanting to limit the range of the two columns to those specific rows (i.e. C5:C24). I know there is a wildcard entry I can input in so it will substitute a value in for each row, but I can't remember for the life of me what that wildcard value is. Am I making sense? Thanks again! -- Teri Albert "Spiky" wrote: On Apr 23, 2:11 pm, tralbert wrote: I'm working with a spreadsheet that in part looks at Trainee retention/turnover. When a trainee leaves, I count it in the total for the area using a COUNTIF function. I also need to acquire a total for each of the two trainers in the area (note the trainees are not sorted by trainer so I don't think using SUBTOTAL would be an option here). An example would be as follows: Trainee Trainer Turnover/Retention Sample 1 Trainer A Y Sample 2 Trainer B N Sample 3 Trainer B Y Sample 4 Trainer A N Total Trainer A Total Trainer B Area Total I tried using the following formula to get the results, but Excel isn't liking it. I'm thinking I don't have the parentheses correct. =SUMIF(C5:C24, "Trainer A",{COUNTIF(I5:I24, "N")}) Anyone have any ideas/opinions on this? Thanks! -- Teri Albert Try this. =SUMPRODUCT((C5:C24="Trainer A")*(I5:I24="N")) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF function within SUMIF
On Apr 23, 3:51 pm, tralbert wrote:
Ok, that will work at least. Thanks! -- Teri Albert "PCLIVE" wrote: With SUMPRODUCT you will have to give it a specific range. However, you can make the range large enough to suit your needs. Example: =SUMPRODUCT((C5:C1000="Trainer A")*(I5:I1000="N")) or =SUMPRODUCT(--(C5:C1000="Trainer A"),--(I5:I1000="N")) HTH, Paul -- "tralbert" wrote in message ... That did the trick! Thanks! Next question if you happen to know, I'm not wanting to limit the range of the two columns to those specific rows (i.e. C5:C24). I know there is a wildcard entry I can input in so it will substitute a value in for each row, but I can't remember for the life of me what that wildcard value is. Am I making sense? Thanks again! -- Teri Albert "Spiky" wrote: On Apr 23, 2:11 pm, tralbert wrote: I'm working with a spreadsheet that in part looks at Trainee retention/turnover. When a trainee leaves, I count it in the total for the area using a COUNTIF function. I also need to acquire a total for each of the two trainers in the area (note the trainees are not sorted by trainer so I don't think using SUBTOTAL would be an option here). An example would be as follows: Trainee Trainer Turnover/Retention Sample 1 Trainer A Y Sample 2 Trainer B N Sample 3 Trainer B Y Sample 4 Trainer A N Total Trainer A Total Trainer B Area Total I tried using the following formula to get the results, but Excel isn't liking it. I'm thinking I don't have the parentheses correct. =SUMIF(C5:C24, "Trainer A",{COUNTIF(I5:I24, "N")}) Anyone have any ideas/opinions on this? Thanks! -- Teri Albert Try this. =SUMPRODUCT((C5:C24="Trainer A")*(I5:I24="N")) If your data changes size, there are ways to make the area dynamic, so that it always includes all the necessary rows. Otherwise, if it stays the same, or within a certain size, probably just easiest to pick a range that will always work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I use a Reference inside a SUMIF or COUNTIF Function? | Excel Worksheet Functions | |||
function question (sumif countif conditional) | Excel Worksheet Functions | |||
countif / sumif function error | Excel Worksheet Functions | |||
Is there a function Countif that operates like Sumif? | Excel Worksheet Functions | |||
Countif/Sumif function question | Excel Worksheet Functions |