counting dependent on date and department

[ljvb]

I have a detail table that shows monthly salaries by month across the top and individual staff members with department down the side . I'm creating a summary table that counts the number of staff in a table by date across the top and department down the side (salary greater than zero).

I've done it to show total salaries by month and deparment using sumif and the index/match combination to set the column (see below for sample) but sadly countif doesn't have the same parameters as sumif.

=SUMIF(StaffDetail!$A$5:$A$103,$A14,INDEX(StaffDetail!$I$5:$BD$5,MATCH(B$13,StaffDetail!$I$1:$BD$1,0)))

Thoughts on how to handle this?

 

[FormR]

Hi, it's a little difficult to visualise your set-up - but based on the SUMIF() maybe this is what you want:

=COUNTIFS(StaffDetail!$A$5:$A$103,$A14,INDEX(StaffDetail!$I$5:$BD$5,MATCH(B$13,StaffDetail!$I$1:$BD$1,0)),">0")

 

[FormR]

Hi, actually - I think this is it, COUNTIFS() does not automatically adjust the ranges if they are not the same size like SUMIF().

=COUNTIFS(StaffDetail!$A$5:$A$103,$A14,INDEX(StaffDetail!$I$5:$BD$103,0,MATCH(B$13,StaffDetail!$I$1:$BD$1,0)),">0")

 

[ljvb]

That's it, thank you! Bit of a tricky one to wrap my head around. Appreciate your help