Forum Discussion

Copper Contributor

Nov 29, 2024

How to count the total value of unique items

Hello! Hoping someone can help. I have a spreadsheet of data that lists projects that come to my team for review/approval. Every time a project comes to us, we are recording the project name, if it...

excel

John_L

Copper Contributor

Dec 03, 2024

Try this... This is the formula in D7: =IF(COUNTIFS(A$2:A7,A7,B$2:B7,B7)>1,"Duplicate","First")

Then you can go straight to your pivot and filter out duplicates.

shelbasaur

Copper Contributor

Dec 05, 2024

This is almost perfect, but would there be any way to count only the last instead of the first? I could possibly reverse sort the entries but if the formal could account for it that would be better.

But I was able to get the formula and pivot table to work in my sheet. Just for example a project may have had an initial estimated value of $50, but then they come back to us later and they are estimating a higher value. Logically, the most recent estimate would be the most accurate info, and I would prefer to be able to tally that entry.

John_L
Copper Contributor
Dec 09, 2024
Sure. Just pin the last row instead of the first. In the example above change =IF(COUNTIFS(A$2:A7,A7,B$2:B7,B7)>1,"Duplicate","First") which pinned row 2 to
=IF(COUNTIFS(A7:A$16,A7,B7:B$16,B7)>1,"Duplicate","Last") where the last row is row 16.
John_L
Copper Contributor
Dec 09, 2024
Sure. Where the formula =IF(COUNTIFS(A$2:A7,A7,B$2:B7,B7)>1,"Duplicate","First") pins row 2 ($2) as the first row, the formula =IF(COUNTIFS(A7:A$[last],A7,B7:B$[last],B7)>1,"Duplicate","Last") would pin the last row if you substitute the row number for [last].
Let me know if you need an example.