Search code examples
google-sheetslambdacountoffsetcountif

Adding more (3rd) criteria to COUNTIFS

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.

Solution

  • try:

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

    enter image description here

    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))))

    enter image description here