Sumifs formula excluding duplicates

Copper Contributor

What I need is a sumifs formula that will not include a value that shows up more than once.


How can I pull the 15.00% only once with criteria 1 employee name and criteria 2 "403B" into a cell on a separate sheet? Right now with sumifs I get 30.00%.









4 Replies



Well, we could conceive of some solution to your question as posed that uses the UNIQUE function.


HOWEVER, before we go there, is it ever possible that employee Barry M. could appear twice with a different % rate in that last column? Or other employees? In other words, how variable are the various columns that are incorporated in this SUMIF formula?

Each time the employee's name appears with the same % rate. Other employees could have the same % rate.
best response confirmed by Hans Vogelaar (MVP)



This achieves the result, but it may not be what you're actually looking for. You had referenced the SUMIF function in your question, but it's not clear at all why you want SUMIF per se. If you just are looking for each individiual's percentage for a given code (e.g., 403B) then there's no summing needed; just a simple matter of data retrieval answering the question "What percentage is associate with Barry M and code 403B?"


This solution does that for each name on a list with several....


If this is not what you're looking for, come back with a more complete example and explanation of both the input or raw data, and the output expected. Leave the matter of the appropriate function to us.

I do not know why I was stuck on SUMIF. Thank you for sending this formula. It worked for what I needed!