using sum & sumifs with a named range

[jynxy]

Hi,

i have this formula which works fine

=200-(SUM(SUMIFS(B6:B405,E6:E405,{"h","ld","u","f"})))

what i need is {"h","ld","u","f"} to be a named range, so that values can be added in on a later date if needed. from what i can is this is not possible is that correct?

Thanks

 

[Fluff]

Do you mean you want to put "h","ld","u","f" into separate cells & use that range in the formula?

 

[jynxy]

Yes that is correct

 

[Fluff]

In that case you do that, like

=200-(SUM(SUMIFS(B6:B405,E6:E405,MyRange)))

just change MyRange to the name of your range

 

[jynxy]

Thanks, i have tried this and weirdly none of the options change the result except the second, i have double checked the named range and it refers to =SETTINGS!$B$2:$B$20 so it should pick them all up?

 

[Fluff]

In that case can you post a sample of the data that shows the problem, along with the values in the named range.

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.

 

[jynxy]

ahh i cant install that from this machine, are you ok to use this link to a sample of the file ?

Sample.xlsm

 

[jynxy]

Sorry forgot to add, goto Jan then the columns for 01/01/2022 you will see 4 under E and Holiday set, if you change Holiday to Say Lieu this changes B3 back to 200 instead of 196

 

[Fluff]

Ok, you will probably need to array enter the formula using Ctrl Shift Enter, or use

=200-(SUMPRODUCT(SUMIFS(B6:B405,E6:E405,L_LEAVE)))

 

[jynxy]

Thanks @Fluff looks like sumproduct worked, here is a ? on me