Sum all only once with column containing potential duplicates

[helpmeplz_]

I have 7 columns that I have "automated," but now they don't like the automation because the dataset must be sorted a to z to function. Can you help me re-automate these functions so the data doesn't need to be sorted a to z?

=IF($A4=$A5,"",SUMIFS($I:$I,$A:$A,$A4))

=IFERROR(IF($A4=$A5,"",SUMIFS($N:$N,$A:$A,$A4))+IF($A4=$A5,"",SUMIFS($P:$P,$A:$A,$A4)),"")

=IF($A4=$A5,"",SUMPRODUCT(($A$4:$A$1000=$A4)*($B$4:$B$1000)*$K$4:$K$1000)/$L4)

Column A is just data that was originally sorted a to z to automate, starting at A4 but would now be unsorted. Trying to not double count data because it's based on column A and column A will now not be sorted.

Column A
A4 Amazon
A5 Amazon
A6 Best Buy
A7 Amazon
A8 Best Buy
A9 Target
A10 Amazon

 

[SanjayGMusafir]

Update your current Excel Version and platform in your profile to think of a solution that suits your version of Excel.

Also, post some (real) sample data using XL2BB to understand the whole scenario.

You can use links to both down here in my signatures for quick access.

 

[helpmeplz_]

I updated it, it's 365 and I have power query and power pivot. I am on Windows. I am trying to add the XL2BB, but having an issue right now because I need admin access from my company

 

[SanjayGMusafir]

I am trying to add the XL2BB, but having an issue right now because I need admin access from my company

Yes that can an issue at times

Can you share a screenshot to understand the scenario - That shall be helpful

 

[SanjayGMusafir]

=IF($A4=$A5,"",SUMIFS($I:$I,$A:$A,$A4))

Check this and revert -

=IF(Countifs($A$4:$A4,$A4)>1,"",SUMIFS($I:$I,$A:$A,$A4))

 

[helpmeplz_]

Here is an example. Checking your formula for the sumifs to see if that resolves, just leaving open the sumproduct

 

[SanjayGMusafir]

Check this and revert -

=IF(Countifs($A$4:$A4,$A4)>1,"",SUMIFS($I:$I,$A:$A,$A4))

Have you tried this, I think it should work.

Idea is to count if there is any duplicate or not...

 

[helpmeplz_]

It looks like it's working! I am now testing and updating all the formulas which are similar.

Can you help with the sumproduct or do I need to just enter IF(Countifs($A$4:$A4,$A4)>1,"",SUMPRODUCT(($A$4:$A$1000=$A4)*($B$4:$B$1000)*$K$4:$K$1000)/$L4)

 

[SanjayGMusafir]

IF(Countifs($A$4:$A4,$A4)>1,"",SUMPRODUCT(($A$4:$A$1000=$A4)*($B$4:$B$1000)*$K$4:$K$1000)/$L4)

I think it should work... Seems correct...

 

[helpmeplz_]

I am having trouble with this one. I tried using two of your functions and/or combining them into the sumifs as one, but it's not working. I got a #Value and #Spill

=IFERROR(IF($A5=$A6,"",SUMIFS($N:$N,$A:$A,$A5))+IF($A5=$A6,"",SUMIFS($P:$P,$A:$A,$A5)),"")