SUMIF and Exclude Duplicates

**hthoma11** · 08-12-2016, 09:59 AM

I am trying to create a formula that will give me the sum of of Column E while allowing duplicates in Column A but not allowing duplicates in Column B and Column C. For example in cell F2 & F6 should have a value of 8, cell F3 should have a value of 3, so on and so forth.
SUMIF and Eliminate Duplicates.PNG

F2 & F6 is 8 because...
Step 1: Column A has 4 options for the date 8/1/2016

SUMIF and Eliminate Duplicates 1.PNG

Step 2: Column C has 3 options for 'P558 Gas BE' matching 8/1/2016 and eliminating any duplicates

SUMIF and Eliminate Duplicates 2.PNG

Step 3: Column B has 2 options matching Step 1 & Step 2 and eliminating any duplicates

SUMIF and Eliminate Duplicates 3.PNG

and the sum of E2 and E7 is 8

I am just not sure how to write a formula for column F that will do this for me. Any help is greatly appreciated.

**teylyn** · 08-12-2016, 11:26 PM

Hello,

create a helper column. That will be much easier to maintain than a huge, complex formula.

Count how many times the data in column B has appeared up to the current row. In cell H2

=countif($B$1:B2,B2)

Copy down.

Now you can create a Sumifs formula with the criterion that column H equals 1.

=SUMIFS($E$2:$E$6,$A$2:$A$6,A2,$C$2:$C$6,C2,$H$2:$H$6,1)

2016-08-13_15-26-17.gif

You can hide the helper column.

cheers, teylyn

**FDibbins** · 08-13-2016, 12:11 AM

For what it's worth, I agree with teylyn's approach here. yes there are some fancy, coplex formulas that will do this for you all in 1 go, but if you dont understand how they work, you wont be able to maintain them later. helpers can be simple and save a lot of effort

SUMIF and Exclude Duplicates

LinkBack

Thread Tools

Rate This Thread

Display

SUMIF and Exclude Duplicates

Re: SUMIF and Exclude Duplicates

Re: SUMIF and Exclude Duplicates

Thread Information

Users Browsing this Thread

Similar Threads

INDEX formula to exclude duplicates

[SOLVED] Attempting to exclude duplicates

SUMIFS and exclude Duplicates

[SOLVED] Sum exclude duplicates if below each other

SUMIFS and exclude Duplicates with a table reference

Sum column exclude duplicates

[SOLVED] Count 350 SS numbers, exclude duplicates

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions