COUNTIF function within SUMIF

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

 

[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"))

 

[Peo Sjoblom]

=SUMPRODUCT(--(C5:C24="Trainer A"),--(I5:I24="N"))



--


Regards,


Peo Sjoblom

 

[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

 

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

 

[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


--

 

[tralbert]

Ok, that will work at least. Thanks!

 

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