CODEWITHSUNDEEP
excelexcel-formulacountuniquecriteria
CuriousGeorge
CuriousGeorge

Reputation: 49

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

Upvotes: 1

Views: 504

Answers (1)

JvdV
JvdV

Reputation: 75840

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

Upvotes: 3

Related Questions