Adding more (3rd) criteria to COUNTIFS

Question

i'm creating a log to record replaced ropes at a climbing gym, with various "averages" output for overview.

i'd like to add an option to filter these "averages" by rope type, selected in a drop-down.

i already have the "averages" displayed using COUNTIFS with two criteria, and it works perfectly.

but when i add this drop-down match criteria (3rd criteria using COUNTIF), i can't seem to make the formula work. adding this 3rd criteria gives faulty output (currently "0", when it should display "1")

i highlighted pertinent cells in turquois. hoping for a simple solution/explanation.

attached file.

i tried adding 3rd criteria to COUNTIFS but it doesn't work.

Answer 1

try:

=INDEX(COUNTIFS(R4:4, TRUE, R3:3&OFFSET(R4:4,,5), K3&F2))

for L4 it will be:

=INDEX(COUNTIFS(R4:4, TRUE, R3:3&OFFSET(R4:4,,4), L3&F2))

for M4 - OFFSET(range,,3) etc.

or if you want to drag it K4:

=INDEX(COUNTIFS($R4:4, TRUE, $R3:3&OFFSET($R4:4,,6-COLUMN(A1)), K3&$F2))

---

but instead of per-cell formula its better to use arrays. use this in K4 after you delete everything in L4:O4 range:

=INDEX(MAP(K3:O3, {5,4,3,2,1}, LAMBDA(x, y, 
 COUNTIFS($R4:4, TRUE, $R3:3&OFFSET($R4:4,,y), x&$F2))))