Forum Discussion
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 was approved or not, the value of the project, and the stage of the project (the stage is very quantifiable it is always a number from L1-L5). We see many projects multiple times, either because we previously rejected it for some reason and now we are able to approve, or because we are approving it at a new stage.
We want to be able to measure the total value of unique approved projects at each stage. So basically filter out anything that is approved and duplicated within the same stage, and then be able to total the values of those items.
So far I've been doing this very manually, by just duplicating all data into a new sheet and removing duplicates, then doing a pivot table based on that new sheet. Is there a better way to do this?
11 Replies
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.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.
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.
Please try this:
- Use a Helper Column to Identify Unique Projects:
- Add a new column to your data to create a unique identifier for each project at each stage. For example, concatenate the project name and stage.
- Formula: =A2 & "-" & D2 (assuming A2 is the project name and D2 is the stage).
- Remove Duplicates:
- Use Excel's Remove Duplicates feature on the helper column to filter out duplicate entries.
- Select the helper column, go to the Data tab, and click Remove Duplicates.
- Filter Approved Projects:
- Use the Filter feature to display only the approved projects.
- Select your data range, go to the Data tab, and click Filter. Then, filter the column that indicates approval status.
- Sum the Values:
- Use the SUMIFS function to calculate the total value of unique approved projects at each stage.
- Formula: =SUMIFS(C:C, B:B, "Approved", D:D, "L1") (assuming C is the value column, B is the approval status column, and D is the stage column).
- Use a Helper Column to Identify Unique Projects:
Hi
We want to be able to measure the total value of unique approved... So basically filter out anything that is approved...
Could you:
- Clarify the above point
- Post of picture (better share a sample representative workbook) showing your data + the expected result
- What version of Excel do you run + on which platform (Windows, Mac, Web...)?
I thought I answered this, but my answers might not be posting.
I am including a screenshot below of basically what my data looks like. A project comes to us for approval at 3 different levels. Each time, we approve, or send it back for feedback. A project could come to us multiple times within a level, (and this can happen even if they were approved the first time). A project also should come to us at each level for approval.
What I am trying to provide to leadership is a view of the value of the projects that are moving through this process. However as I'm sure you can understand, if we count an initiative multiple times, those values will be significantly inflated. So I have been going through manually to remove duplications before doing the weekly totals.
Including a screenshot below. It's hard to explain without over complicating it, but basically all projects have to go through 3 "levels" of funding, resources, etc. Each time a project is submitted to us, we can approve it, or send it back for feedback. A project can come to us at the same "approval level" multiple times, if either we rejected them the first time, or sometimes even if we approve them but then their values/costs change significantly and they need to be reviewed again. For things that come to us multiple times in the same "Approval Level," I really only care about the most recent entry.
A project can also come to us multiple times as it moves through each level. So we may have approved it at SL2, and now we are approving it at SL3. If a project is approved 3x but they were in different levels (SL1,SL2,SL3) then I want to count each of those.
What I typically am providing to leadership weekly is the Total NRB for SL2 Approvals, Total NRB for SL2 Feedback Requested, Total NRB for SL3 Approvals, and Total NRB for SL3 Feedback Requested. Because I don't want to include duplications of values (you can see how that would quickly get out of hand), I am manually removing duplicates and only tallying the most recent entry.
Also my version is on Windows and it's listed as Microsoft Excel for Microsoft 365 MSO.
Something remains unclear to me... See if the attached Power Query option (must be Refreshed when data change in SourceTable) does it
I assume, you need GROUPBY() function. See the attached file and let us know your feedback.
=GROUPBY(B2:C17,D2:D17,SUM,3,1)Not quite, this looks like you are totally the value of the same project within the same stage. I am trying to remove those as duplicates and only total non duplicative projects.
