Hi All
I have quite a large data(150K rows) of invoice references and amounts and I've added a conditional format to indicated duplicate references. I'm struggling to get a formula to calculate the total unique number of transactions and amount if he conditional format is "yes" in other words its a duplicate. Grateful if anybody could help.
Many thanks
Try the following array formula
{=SUM(IF(D5:D13="Yes",C5:C13))}
Ctl+Shift+Enter
HTH
Hi
Use this formula in D5 to see duplicates and copy down
Formula:
=IF(COUNTIF(C$5:C5,C5)>1,"Yes","No")
Use the following formulas to get the results
Sum of Amount of Unique Transaction if conditional format is "yes"(duplicate)
Formula:=SUMIF(D5:D13,"Yes",C5:C13)
Total number of UniqueTransactions if conditional format is "yes"(duplicate)
Formula:=COUNTIF(D5:D13,"Yes")
Originally Posted by José Augusto
After you copy that formula to 150,000 rows this is how many cells you're referencing:I have quite a large data(150K rows)
=SUMPRODUCT(ROW(A1:A150000))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Try
D5=IF(SUMPRODUCT(--($B$5:$B$13&$C$5:$C$13=B5&C5))>1,"Yes","NO")
J17=SUM(IF(FREQUENCY(IF(D5:D13="Yes",MATCH(B5:B13,B5:B13,0),0),ROW(B5:B13)-ROW(B5)+1)>0,C5:C13,0)) with control+Shift+Enter
J18=SUM(--(FREQUENCY(IF(D5:D13="Yes",MATCH(B5:B13,B5:B13,0),0),ROW(B5:B13)-ROW(B5)+1)>0)) with control+Shift+Enter.
Check the attachment.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Guys, this is awesome! Shukla I used your formula and it works pretty cool. Many thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks