COUNTIF function within SUMIF

T

tralbert

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!
 
S

Spiky

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!

Try this.
=SUMPRODUCT((C5:C24="Trainer A")*(I5:I24="N"))
 
P

PCLIVE

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
 
T

tralbert

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!
 
P

PCLIVE

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


--
 
S

Spiky

Ok, that will work at least. Thanks!

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top