View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tralbert tralbert is offline
external usenet poster
 
Posts: 18
Default 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"))