I have a spreadsheet that lists project bids made by different individuals. Occasionally, the same individual may bid on the same project multiple times with various companies, leading to duplicates in the bid totals. My goal is to determine the total amount each individual is bidding while excluding any duplicates.
Here’s a simplified version of my dataset:
Individual
Business
Project
Bid Value
John
Company A
Project A
$10
John
Company B
Project A
$10
John
Company C
Project B
$20
Bob
Company D
Project C
$15
I used this formula for John:
=if(Bidder Column = "John", sum(unique(Bid Value Column)), "Error")
And for Bob I applied:
=if(Bidder Column = "Bob", sum(unique(Bid Value Column)), "Error")
Here’s the expected outcome:
Individual
Total Bid Value
John
$30
Bob
$15
However, the results I am receiving are:
Individual
Total Bid Value
John
$45
Bob
ERROR
It appears that the formula for John sums every unique entry in the bid column, failing to consider the specific individual. As for Bob, it returns an error as if the logical condition isn’t recognizing his name. I have thoroughly reviewed my formulas and the dataset but can’t find the solution to this issue.
i think ur formula’s logic is backwards - try =SUMIF(A:A,"John",D:D) instead. the issue is UNIQUE() grabs all unique values from the column, not just for john. also check for extra spaces around bob’s name, thats probably why ur getting the error.
The problem stems from how you’re applying the UNIQUE function - it’s operating on the entire bid column rather than filtering by individual first. Instead of your current approach, you should use SUMIFS with additional criteria to handle duplicates. Try this formula: =SUMIFS(D:D,A:A,"John",B:B,B:B) won’t work either because it still counts duplicates. What you actually need is something like =SUMPRODUCT(--(A:A="John"),--(COUNTIFS(A:A,A:A,C:C,C:C,ROW(A:A),"<="&ROW(A:A))=1),D:D) but there’s an easier approach. Create a helper column that concatenates Individual and Project, then use SUMIF on unique combinations. For Bob’s error, check if there are any non-printable characters in the cell - copy his name to a new cell and retype it manually to test.
Your current approach has the conditional logic mixed up. The UNIQUE function is pulling all distinct values from the entire bid column regardless of the individual check. What you need is to filter first, then sum. Try using =SUMPRODUCT((A:A="John")*(COUNTIFS(A:A,A:A,C:C,C:C,ROW(A:A),"<="&ROW(A:A))=1)*D:D) which will sum unique combinations of individual and project. This formula checks for the first occurrence of each individual-project pair and sums only those bid values. For Bob’s error, there’s likely a trailing space or formatting issue in the cell containing his name. Double-check the exact spelling and consider using TRIM function to remove extra spaces.