SUM Array formula

[johns99]

Hello,

I am currently using an array formula to pull the sum of two columns based on two separate criteria:

{=SUM((details!$T$3:$T$1724+details!$V$3:$V$1724)*(EEID='C&B - Compensation Data'!$A3))}

This approach is currently working FYI.

The concern with the above formula is if the details are sorted it then throws the formula off, so I am looking to replicate the above without the possibility of the formula being deterred. I'm thinking a SUMIFS but I'm not sure how to do this with 2 whole columns.

Thanks in advance.

John

 

[jasonb75]

What does EEID refer to?

As long as it is a parallel range that is sorted with the 2 'details' ranges, there should be no problems with sorting. If it is an independent range then you would need a common key in both ranges to even have a chance of making it work, without that it will not be possible.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what is EEID?

 

[johns99]

Will do, EEID is the employee ID. Excel version is 2102.

 

[johns99]

Sorry, should've been more specific I am working in MS 365, thanks.

 

[Fluff]

Thanks for updating your profile.

EEID is the employee ID

Yes, but is it a single cell, or a range? If it's a range, what is the range?

 

[jasonb75]

Referring back to my earlier reply (post 2), we are going to need some additional information and clarity if we are to have any hop in finding a solution. That said, if your existing formula is not working correctly when the data is sorted then any alternative is not likely to work correctly either.

 

[johns99]

Thanks for updating your profile.

Yes, but is it a single cell, or a range? If it's a range, what is the range?

 

[johns99]

EEID is a named range: =details!$C$3:$C$1724

 

[Fluff]

Ok, when you talk about sorting, are you referring to the Details sheet or the C&B - Compensation Data sheet?
If the latter then remove the sheet name from the formula.