Search code examples
excelexcel-formulacountuniquecriteria

Finding a more efficient way of using SUM PRODUCT and COUNTIF

I currently have this formula which works but it takes a few seconds for excel to catch up, and I was wondering if anyone knew about a more efficient way of handling this. Aside for it taking a few seconds to process when I drag the formula down excel doesn't update the new cells and eventually crashes the entire file.

=SUMPRODUCT(((Paste!$B$2:$B$12000=A2))/COUNTIFS(Paste!$B$2:$B$12000,Paste!$B$2:$B$12000&"",Paste!$C$2:$C$12000,Paste!$C$2:$C$12000&""))

Sample Data: enter image description here

Solution

  • Let's imagine the data layed out in front of us as you present:

    enter image description here

    1) Formulas Without DA-functionality:

    You could apply this method:

    In F3:

    =SUM(--(FREQUENCY(IF(A$3:A$9=E3,MATCH(B$3:B$9,B$3:B$9,0)),ROW(B$3:B$9)-2)>0))

    Enter through CtrlShiftEnter

    2) With DA-functionality

    You could apply this method:

    In F3:

    =COUNTA(UNIQUE(FILTER(B3:B9,A3:A9=A2)))

    Adapt any of the above to fit your ranges and needs, but do remember array formula may just way heavily on calculation.

    3) Pivot Table:

    If you want to avoid formulas alltogether, simply select all your data e.g. A2:B9 and follow these steps:

    • Click tab Insert from the ribbon and choose PivotTable
    • Choose where to insert the resulting table and check "Add this data to the Data Model"
    • Use "Process" as column, and "Test_ID" as values.
    • Go to "Value Field Settings" and summarize values by "Distinct Count"
    • Confirm and enjoy the resulting table:

    enter image description here