Search code examples
excelexcel-formulapivot-tableexcel-2010

Recreate a basic table pivot (count of 2 data columns) via formula

I have my data in the column AI and AJ as attached in the screenshot. I want to automate some process due to which, I want my final outcome as per type AM and AN (after applying pivot). I tried but failed in many attempts. Please help and solve my doubt.enter image description here

Solution

  • In a new sheet, paste this:

    Department Tag Index of unique department Index of unique Department-Tag match Unique index of Department-Tag match (Department part) Unique index of Department-Tag match (Tag part) Internal row of unique department Internal row of unique Department-Tag match Department (sorted) Tag (sorted) Department-tag count Paragraph count Paragraph label Paragraph label/point index Row labels Count of Tag Conditional formatting Empty row Conditional formatting Grand Total Conditional formatting Paragraph label Conditional formatting Point label
    Distribution Incorrect delivery Values count =COUNTA(A:A) =IFERROR(MATCH(A2,INDIRECT($E$9),0),"") =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A2)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B2))^0),1) =IF(COUNTIFS($G$2:G2,G2,$H$2:H2,H2)>1,"",G2) =IF(I2="","",H2) =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K2)-ROW($K$2)+1),"") =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K2),COUNTIF($K$2:K2,K2)),"") =IFERROR(INDEX(A:A,K2),"") =IFERROR(INDEX(B:B,L2),"") =IF(M2="","",COUNTIFS(A:A,M2,B:B,N2)) =AND(M3<>M2,M3<>"")*1 =OFFSET(M2,-SUM($P$2:P2),0) =IF(OR(Q2=Q1,AND(Q2<>Q1,Q2="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R1),"",MAX($R$1:R1)+1),Q2) =IF(AND(Q2="",Q1="",COUNTIF($T$1:T1,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R2),INDEX(INDIRECT($E$13),$R2),R2)) =IF(T2="Grand Total",SUM($U$1:U1),IF(ISNUMBER($R2),INDEX(INDIRECT($E$14),$R2),"")) =$T2="" =$T2="Grand Total" =AND(NOT(ISNUMBER($R2)),$R2<>"",$R2<>"Grand Total") =ISNUMBER($R2)
    Quality Smelling Column 1 =LEFT(CELL("address",A1),FIND("$",CELL("address",A1),2)) =IFERROR(MATCH(A3,INDIRECT($E$9),0),"") =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A3)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B3))^0),1) =IF(COUNTIFS($G$2:G3,G3,$H$2:H3,H3)>1,"",G3) =IF(I3="","",H3) =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K3)-ROW($K$2)+1),"") =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K3),COUNTIF($K$2:K3,K3)),"") =IFERROR(INDEX(A:A,K3),"") =IFERROR(INDEX(B:B,L3),"") =IF(M3="","",COUNTIFS(A:A,M3,B:B,N3)) =AND(M4<>M3,M4<>"")*1 =OFFSET(M3,-SUM($P$2:P3),0) =IF(OR(Q3=Q2,AND(Q3<>Q2,Q3="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R2),"",MAX($R$1:R2)+1),Q3) =IF(AND(Q3="",Q2="",COUNTIF($T$1:T2,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R3),INDEX(INDIRECT($E$13),$R3),R3)) =IF(T3="Grand Total",SUM($U$1:U2),IF(ISNUMBER($R3),INDEX(INDIRECT($E$14),$R3),"")) =$T3="" =$T3="Grand Total" =AND(NOT(ISNUMBER($R3)),$R3<>"",$R3<>"Grand Total") =ISNUMBER($R3)
    Quality Taste issue Column 2 =LEFT(CELL("address",B1),FIND("$",CELL("address",B1),2)) =IFERROR(MATCH(A4,INDIRECT($E$9),0),"") =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A4)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B4))^0),1) =IF(COUNTIFS($G$2:G4,G4,$H$2:H4,H4)>1,"",G4) =IF(I4="","",H4) =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K4)-ROW($K$2)+1),"") =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K4),COUNTIF($K$2:K4,K4)),"") =IFERROR(INDEX(A:A,K4),"") =IFERROR(INDEX(B:B,L4),"") =IF(M4="","",COUNTIFS(A:A,M4,B:B,N4)) =AND(M5<>M4,M5<>"")*1 =OFFSET(M4,-SUM($P$2:P4),0) =IF(OR(Q4=Q3,AND(Q4<>Q3,Q4="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R3),"",MAX($R$1:R3)+1),Q4) =IF(AND(Q4="",Q3="",COUNTIF($T$1:T3,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R4),INDEX(INDIRECT($E$13),$R4),R4)) =IF(T4="Grand Total",SUM($U$1:U3),IF(ISNUMBER($R4),INDEX(INDIRECT($E$14),$R4),"")) =$T4="" =$T4="Grand Total" =AND(NOT(ISNUMBER($R4)),$R4<>"",$R4<>"Grand Total") =ISNUMBER($R4)
    Distribution Delivery skipped Column 3 =LEFT(CELL("address",I2),FIND("$",CELL("address",I2),2)) =IFERROR(MATCH(A5,INDIRECT($E$9),0),"") =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A5)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B5))^0),1) =IF(COUNTIFS($G$2:G5,G5,$H$2:H5,H5)>1,"",G5) =IF(I5="","",H5) =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K5)-ROW($K$2)+1),"") =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K5),COUNTIF($K$2:K5,K5)),"") =IFERROR(INDEX(A:A,K5),"") =IFERROR(INDEX(B:B,L5),"") =IF(M5="","",COUNTIFS(A:A,M5,B:B,N5)) =AND(M6<>M5,M6<>"")*1 =OFFSET(M5,-SUM($P$2:P5),0) =IF(OR(Q5=Q4,AND(Q5<>Q4,Q5="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R4),"",MAX($R$1:R4)+1),Q5) =IF(AND(Q5="",Q4="",COUNTIF($T$1:T4,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R5),INDEX(INDIRECT($E$13),$R5),R5)) =IF(T5="Grand Total",SUM($U$1:U4),IF(ISNUMBER($R5),INDEX(INDIRECT($E$14),$R5),"")) =$T5="" =$T5="Grand Total" =AND(NOT(ISNUMBER($R5)),$R5<>"",$R5<>"Grand Total") =ISNUMBER($R5)
    Distribution Late Dely Column 4 =LEFT(CELL("address",J2),FIND("$",CELL("address",J2),2)) =IFERROR(MATCH(A6,INDIRECT($E$9),0),"") =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A6)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B6))^0),1) =IF(COUNTIFS($G$2:G6,G6,$H$2:H6,H6)>1,"",G6) =IF(I6="","",H6) =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K6)-ROW($K$2)+1),"") =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K6),COUNTIF($K$2:K6,K6)),"") =IFERROR(INDEX(A:A,K6),"") =IFERROR(INDEX(B:B,L6),"") =IF(M6="","",COUNTIFS(A:A,M6,B:B,N6)) =AND(M7<>M6,M7<>"")*1 =OFFSET(M6,-SUM($P$2:P6),0) =IF(OR(Q6=Q5,AND(Q6<>Q5,Q6="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R5),"",MAX($R$1:R5)+1),Q6) =IF(AND(Q6="",Q5="",COUNTIF($T$1:T5,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R6),INDEX(INDIRECT($E$13),$R6),R6)) =IF(T6="Grand Total",SUM($U$1:U5),IF(ISNUMBER($R6),INDEX(INDIRECT($E$14),$R6),"")) =$T6="" =$T6="Grand Total" =AND(NOT(ISNUMBER($R6)),$R6<>"",$R6<>"Grand Total") =ISNUMBER($R6)
    CC Agent issue Column 5 =LEFT(CELL("address",N2),FIND("$",CELL("address",N2),2)) =IFERROR(MATCH(A7,INDIRECT($E$9),0),"") =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A7)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B7))^0),1) =IF(COUNTIFS($G$2:G7,G7,$H$2:H7,H7)>1,"",G7) =IF(I7="","",H7) =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K7)-ROW($K$2)+1),"") =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K7),COUNTIF($K$2:K7,K7)),"") =IFERROR(INDEX(A:A,K7),"") =IFERROR(INDEX(B:B,L7),"") =IF(M7="","",COUNTIFS(A:A,M7,B:B,N7)) =AND(M8<>M7,M8<>"")*1 =OFFSET(M7,-SUM($P$2:P7),0) =IF(OR(Q7=Q6,AND(Q7<>Q6,Q7="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R6),"",MAX($R$1:R6)+1),Q7) =IF(AND(Q7="",Q6="",COUNTIF($T$1:T6,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R7),INDEX(INDIRECT($E$13),$R7),R7)) =IF(T7="Grand Total",SUM($U$1:U6),IF(ISNUMBER($R7),INDEX(INDIRECT($E$14),$R7),"")) =$T7="" =$T7="Grand Total" =AND(NOT(ISNUMBER($R7)),$R7<>"",$R7<>"Grand Total") =ISNUMBER($R7)
    Distribution Delivery skipped Column 6 =LEFT(CELL("address",O2),FIND("$",CELL("address",O2),2)) =IFERROR(MATCH(A8,INDIRECT($E$9),0),"") =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A8)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B8))^0),1) =IF(COUNTIFS($G$2:G8,G8,$H$2:H8,H8)>1,"",G8) =IF(I8="","",H8) =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K8)-ROW($K$2)+1),"") =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K8),COUNTIF($K$2:K8,K8)),"") =IFERROR(INDEX(A:A,K8),"") =IFERROR(INDEX(B:B,L8),"") =IF(M8="","",COUNTIFS(A:A,M8,B:B,N8)) =AND(M9<>M8,M9<>"")*1 =OFFSET(M8,-SUM($P$2:P8),0) =IF(OR(Q8=Q7,AND(Q8<>Q7,Q8="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R7),"",MAX($R$1:R7)+1),Q8) =IF(AND(Q8="",Q7="",COUNTIF($T$1:T7,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R8),INDEX(INDIRECT($E$13),$R8),R8)) =IF(T8="Grand Total",SUM($U$1:U7),IF(ISNUMBER($R8),INDEX(INDIRECT($E$14),$R8),"")) =$T8="" =$T8="Grand Total" =AND(NOT(ISNUMBER($R8)),$R8<>"",$R8<>"Grand Total") =ISNUMBER($R8)
    Distribution Outside leakage Range 1 =E3&"1:"&E3&$E$2 =IFERROR(MATCH(A9,INDIRECT($E$9),0),"") =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A9)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B9))^0),1) =IF(COUNTIFS($G$2:G9,G9,$H$2:H9,H9)>1,"",G9) =IF(I9="","",H9) =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K9)-ROW($K$2)+1),"") =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K9),COUNTIF($K$2:K9,K9)),"") =IFERROR(INDEX(A:A,K9),"") =IFERROR(INDEX(B:B,L9),"") =IF(M9="","",COUNTIFS(A:A,M9,B:B,N9)) =AND(M10<>M9,M10<>"")*1 =OFFSET(M9,-SUM($P$2:P9),0) =IF(OR(Q9=Q8,AND(Q9<>Q8,Q9="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R8),"",MAX($R$1:R8)+1),Q9) =IF(AND(Q9="",Q8="",COUNTIF($T$1:T8,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R9),INDEX(INDIRECT($E$13),$R9),R9)) =IF(T9="Grand Total",SUM($U$1:U8),IF(ISNUMBER($R9),INDEX(INDIRECT($E$14),$R9),"")) =$T9="" =$T9="Grand Total" =AND(NOT(ISNUMBER($R9)),$R9<>"",$R9<>"Grand Total") =ISNUMBER($R9)
    Quality Inside leakage Range 2 =E4&"1:"&E4&$E$2 =IFERROR(MATCH(A10,INDIRECT($E$9),0),"") =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A10)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B10))^0),1) =IF(COUNTIFS($G$2:G10,G10,$H$2:H10,H10)>1,"",G10) =IF(I10="","",H10) =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K10)-ROW($K$2)+1),"") =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K10),COUNTIF($K$2:K10,K10)),"") =IFERROR(INDEX(A:A,K10),"") =IFERROR(INDEX(B:B,L10),"") =IF(M10="","",COUNTIFS(A:A,M10,B:B,N10)) =AND(M11<>M10,M11<>"")*1 =OFFSET(M10,-SUM($P$2:P10),0) =IF(OR(Q10=Q9,AND(Q10<>Q9,Q10="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R9),"",MAX($R$1:R9)+1),Q10) =IF(AND(Q10="",Q9="",COUNTIF($T$1:T9,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R10),INDEX(INDIRECT($E$13),$R10),R10)) =IF(T10="Grand Total",SUM($U$1:U9),IF(ISNUMBER($R10),INDEX(INDIRECT($E$14),$R10),"")) =$T10="" =$T10="Grand Total" =AND(NOT(ISNUMBER($R10)),$R10<>"",$R10<>"Grand Total") =ISNUMBER($R10)
    Quality Inside leakage Range 3 =E5&"2:"&E5&$E$2 =IFERROR(MATCH(A11,INDIRECT($E$9),0),"") =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A11)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B11))^0),1) =IF(COUNTIFS($G$2:G11,G11,$H$2:H11,H11)>1,"",G11) =IF(I11="","",H11) =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K11)-ROW($K$2)+1),"") =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K11),COUNTIF($K$2:K11,K11)),"") =IFERROR(INDEX(A:A,K11),"") =IFERROR(INDEX(B:B,L11),"") =IF(M11="","",COUNTIFS(A:A,M11,B:B,N11)) =AND(M12<>M11,M12<>"")*1 =OFFSET(M11,-SUM($P$2:P11),0) =IF(OR(Q11=Q10,AND(Q11<>Q10,Q11="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R10),"",MAX($R$1:R10)+1),Q11) =IF(AND(Q11="",Q10="",COUNTIF($T$1:T10,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R11),INDEX(INDIRECT($E$13),$R11),R11)) =IF(T11="Grand Total",SUM($U$1:U10),IF(ISNUMBER($R11),INDEX(INDIRECT($E$14),$R11),"")) =$T11="" =$T11="Grand Total" =AND(NOT(ISNUMBER($R11)),$R11<>"",$R11<>"Grand Total") =ISNUMBER($R11)
    Distribution Outside leakage Range 4 =E6&"2:"&E6&$E$2 =IFERROR(MATCH(A12,INDIRECT($E$9),0),"") =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A12)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B12))^0),1) =IF(COUNTIFS($G$2:G12,G12,$H$2:H12,H12)>1,"",G12) =IF(I12="","",H12) =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K12)-ROW($K$2)+1),"") =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K12),COUNTIF($K$2:K12,K12)),"") =IFERROR(INDEX(A:A,K12),"") =IFERROR(INDEX(B:B,L12),"") =IF(M12="","",COUNTIFS(A:A,M12,B:B,N12)) =AND(M13<>M12,M13<>"")*1 =OFFSET(M12,-SUM($P$2:P12),0) =IF(OR(Q12=Q11,AND(Q12<>Q11,Q12="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R11),"",MAX($R$1:R11)+1),Q12) =IF(AND(Q12="",Q11="",COUNTIF($T$1:T11,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R12),INDEX(INDIRECT($E$13),$R12),R12)) =IF(T12="Grand Total",SUM($U$1:U11),IF(ISNUMBER($R12),INDEX(INDIRECT($E$14),$R12),"")) =$T12="" =$T12="Grand Total" =AND(NOT(ISNUMBER($R12)),$R12<>"",$R12<>"Grand Total") =ISNUMBER($R12)
    Quality Inside leakage Range 5 =E7&"2:"&E7&$E$2 =IFERROR(MATCH(A13,INDIRECT($E$9),0),"") =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A13)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B13))^0),1) =IF(COUNTIFS($G$2:G13,G13,$H$2:H13,H13)>1,"",G13) =IF(I13="","",H13) =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K13)-ROW($K$2)+1),"") =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K13),COUNTIF($K$2:K13,K13)),"") =IFERROR(INDEX(A:A,K13),"") =IFERROR(INDEX(B:B,L13),"") =IF(M13="","",COUNTIFS(A:A,M13,B:B,N13)) =AND(M14<>M13,M14<>"")*1 =OFFSET(M13,-SUM($P$2:P13),0) =IF(OR(Q13=Q12,AND(Q13<>Q12,Q13="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R12),"",MAX($R$1:R12)+1),Q13) =IF(AND(Q13="",Q12="",COUNTIF($T$1:T12,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R13),INDEX(INDIRECT($E$13),$R13),R13)) =IF(T13="Grand Total",SUM($U$1:U12),IF(ISNUMBER($R13),INDEX(INDIRECT($E$14),$R13),"")) =$T13="" =$T13="Grand Total" =AND(NOT(ISNUMBER($R13)),$R13<>"",$R13<>"Grand Total") =ISNUMBER($R13)
    Distribution Outside leakage Range 6 =E8&"2:"&E8&$E$2 =IFERROR(MATCH(A14,INDIRECT($E$9),0),"") =AGGREGATE(15,6,ROW(INDIRECT($E$9))/(INDIRECT($E$9)=A14)/((ROW(INDIRECT($E$10))*(INDIRECT($E$10)=B14))^0),1) =IF(COUNTIFS($G$2:G14,G14,$H$2:H14,H14)>1,"",G14) =IF(I14="","",H14) =IFERROR(AGGREGATE(15,6,INDIRECT($E$11),ROW(K14)-ROW($K$2)+1),"") =IFERROR(AGGREGATE(15,6,INDIRECT($E$12)/(INDIRECT($E$11)=K14),COUNTIF($K$2:K14,K14)),"") =IFERROR(INDEX(A:A,K14),"") =IFERROR(INDEX(B:B,L14),"") =IF(M14="","",COUNTIFS(A:A,M14,B:B,N14)) =AND(M15<>M14,M15<>"")*1 =OFFSET(M14,-SUM($P$2:P14),0) =IF(OR(Q14=Q13,AND(Q14<>Q13,Q14="")),IF(COUNT(INDIRECT($E$14))=MAX($R$1:R13),"",MAX($R$1:R13)+1),Q14) =IF(AND(Q14="",Q13="",COUNTIF($T$1:T13,"Grand Total")=0),"Grand Total",IF(ISNUMBER($R14),INDEX(INDIRECT($E$13),$R14),R14)) =IF(T14="Grand Total",SUM($U$1:U13),IF(ISNUMBER($R14),INDEX(INDIRECT($E$14),$R14),"")) =$T14="" =$T14="Grand Total" =AND(NOT(ISNUMBER($R14)),$R14<>"",$R14<>"Grand Total") =ISNUMBER($R14)
    Formulas count =COUNTA(G:G)
    Check =IF(AND(COUNTIF(T:T,"Grand Total")=1, E16>=E2),"OK","Add more rows")

    You should obtain something like this (formats not included): enter image description here

    Range Purpose
    A:B The input
    E2 Counts the number of values in column A
    D3:E14 Dynamic ranges
    E16 Counts the number of values in column G
    E17 Checks if E16 is less than E2; it will tell you if you have to drag down the formulas in range G:U
    G:R The "behind the scene" formulas; check the headers
    T:U The result
    W:Z Formulas for the conditional formatting of T2:U14; you don't need them for the result to be returned, but you can use them to apply conditional formatting to the result

    But, seriously: a pivot table is way better than all of this. No idea why you would go with formulas.