adding another criteria

[Patcheen]

Hi all

im trying to add 1 more criteria to this formula but cant seem to work out what exactly i need.

this is the current criteria
=IF(I5<>"home","",COUNTIFS($I$5:$I5,$I5,$K$5:$K5,$K5))

i is the home / Away column

k is the score result 3-1 ext etc

what i want to add is the league to the above criteria

in column N i have the league name LC

how do i add the above column to the above criteria

i tried this but to no avial

=IF(I5<>"home","",COUNTIFS($I$5:$I5,$I5,$K$5:$K5,$K5,N5:N5,L2)) (L2 is the reference to where all the games that match L2 (LC in column N)

TIA

 

[DanteAmor]

Without test data I can only assume:

=IF(I5<>"home","",COUNTIFS($I$5:$I5,$I5,$K$5:$K5,$K5,$N5:$N5,$L$2))

 

[Peter_SSs]

i tried this but to no avial

=IF(I5<>"home","",COUNTIFS($I$5:$I5,$I5,$K$5:$K5,$K5,N5:N5,L2))

I think that you may need to add these two red $ signs into your formula.
=IF(I5<>"home","",COUNTIFS($I$5:$I5,$I5,$K$5:$K5,$K5,N$5:N5,L$2))

 

[Patcheen]

Without test data I can only assume:

=IF(I5<>"home","",COUNTIFS($I$5:$I5,$I5,$K$5:$K5,$K5,$N5:$N5,$L$2))

Thanks Dante but this only threw up the #Value! i know it wasnt easy without a sample workbook and thank you for taking the time for trying to help. Appreciated.

 

[Patcheen]

I think that you may need to add these two red $ signs into your formula.
=IF(I5<>"home","",COUNTIFS($I$5:$I5,$I5,$K$5:$K5,$K5,N$5:N5,L$2))

Thank you so much Peter for your solution it worked a treat and thank you for taking the time for helping. Appreciated.

 

[Peter_SSs]

You are welcome. Thanks for giving feedback on the suggestions.