Sum Cells and not include duplicates

[PTHops]

I have a spreadsheet that includes a column for annual salary. A row can have multiple entries for an individual employee based on dates, when they receive a raise, act in another position. I need to calculate only the 1 line of yearly salary (not duplicates)

Example:

1) Rows 3 and 4 - First row April 1 to 18th and 2nd row, however I only want to capture the 1 amount under Column I as my annual salary risk.
2) Rows 17 and 18 - The employee received a raise mid-year (row 17 for the pre-raise salary and row 18 for the raise. I only want to capture the amount in I18 as my ongoing salary risk (ongoing)

Any insight into the best way to approach this so I am not double counting salary per individual? I believe I can use the Position Number in Column C as the filter number. Basically, take the number in Column I from the last line of duplicate position numbers and if C is Indeterminate, or planned Indeterminate.

A sum of the column now is $1,614,310. Using the right filters it would be $658,499. Thank you in advnace!

 

[kweaver]

Here's one way:

=SUM(1*TEXTSPLIT(UNIQUE(I2:I20&"@"&K2:K20&L2:L20),"@"))

 

[DanteAmor]

How about:

=LET(a,K2:K20,b,E2:E20,SUM(BYROW(UNIQUE(FILTER(a,ISNUMBER(SEARCH("Indeterminate",C2:C20)))),LAMBDA(br,MAXIFS(I2:I20,b,MAXIFS(b,a,br),a,br)))))

 

[kweaver]

I think Dante has that correct where I didn't catch the last condition.

 

[DanteAmor]

In addition to the condition, the last date must be considered, or at least that's what I understood.
When the condition is met, only one value must be considered, and that is where I suppose the one with the greatest date should be.

Let's wait for the comments


By the way, @PTHops,

Note XL2BB:​

For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

---

 

[PTHops]

@DanteAmor I am in in an environment that I am unable to include Ad-Ins in my Excel, unfortunately. Please help me to understand the formula. I am currently receiving error #CALC! when using your formula. I am also wondering if we can base use column B instead of K (employees may have the same last name, so the better criteria to use is the position number?)

LET - this help to assign a letter to a calculation for reference in the rest of the formula? a = K2:20, b = E2:E20 (smart to use the end date, by the way).
SUM - precedes formula with conditions to sum Column I
BYROW - We are looking to look in rows for what we want to SUM?
UNIQUE - no duplicates by row
FILTER - a (K2:K20 - is this where I can change to look in B for the position number?) is looking for a numerical value I2:I20 when we search for "Indeterminate" in C2:C20
LAMBDA - this is new to me. What are we accomplishing here? LAMBDA(br,MAXIFS(I2:I20,b,MAXIFS(b,a,br),a,br) I can see where we are looking in I2:20 for the max amount based on the end date E2:E20. Not sure where "br" fits in

I very much appreciate your assistance. Hoping to learn as I ask for help

 

[DanteAmor]

so the better criteria to use is the position number

Use this:

=LET(a,B2:B20,b,E2:E20,SUM(BYROW(UNIQUE(FILTER(a,ISNUMBER(SEARCH("Indeterminate",C2:C20)))),LAMBDA(br,MAXIFS(I2:I20,b,MAXIFS(b,a,br),a,br)))))

- UNIQUE(FILTER(a,ISNUMBER(SEARCH("Indeterminate",C2:C20)))) .- Filters the data in column B that contains the word "Indeterminate" in column C and gets the unique ones.

- BYROW( .... LAMBDA(br,MAXIFS(I2:I20,b,MAXIFS(b,a,br),a,br))).- For each item obtained in the previous formula, it obtains the maximum value of column I, according to 1 criterion: be the value of the maximum date "b,MAXIFS(b,a,br)". And of course, correspond to the position number "a,br".

-------------​

I am currently receiving error #CALC! when using your formula.

I reviewed the formula and it works fine for me with the sample data you provided.
Try again with a small sample of data. Check that there are numbers in the Salary column and that there are dates in the End Date column. That the data ranges are equal, in my example all the ranges are in row 20.
Check that each of the functions exist in your version.

-------------​

I hope that with the explanation and recommendations the formula works for you.

 

[PTHops]

@DanteAmor I keep getting the Nested Array error even with a smaller subset. I am using Office 365.

=LET(a,A29:A36,b,C29:C36,SUM(BYROW(UNIQUE(FILTER(a,ISNUMBER(SEARCH("Indeterminate",B29:B36)))),LAMBDA(br,MAXIFS(D29:D36,b,MAXIFS(b,a,br),a,br)))))

 

[Fluff]

Can you post some sample data rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[DanteAmor]

The problem is that you are playing with the columns. First you had the position in column B and the salary in column I.

Now you have them in other columns and in your image neither the rows nor the columns of the sheet can be seen.

Can you provide a consistent example?

-------------------------------------
With the new columns it works for me:

Maybe it is a problem in your data or the version of ms365 that you have.