=COUNTIFS with multiple criteria but only needing one met

0
votes

=COUNTIFS(E6:E53,D60,P6:P53,"<>",S6:S53,"<>")

I'm currently trying to get the number of initials in the range E6:E53 that is stored in D60.

I also only want to count the initials if the ranges P6:P53 & S6:S53 has any value in the cell.

With the formula I have it is currently just counting the initials without paying attention to the other two criteria.

Any help is much appreciated.

1
excelexcel-formula

1 Answers

0
votes

Use SUMPRODUCT:

If you want if P6:P53 AND S6:S53 are not Blank:

=SUMPRODUCT((E6:E53=D60)*(LEN(P6:P53)>0)*(LEN(S6:S53)>0))

If you want if P6:P53 OR S6:S53 are not Blank:

=SUMPRODUCT((E6:E53=D60)*((LEN(P6:P53)>0)+(LEN(S6:S53)>0)>0))