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

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

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

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

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

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

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

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.