sum ifs multiple criteria and between values

[IvanYerk]

This is only my 2nd post, but the 1st one was so helpful, I'm going back to the well. I have a formula that isn't working the way I think it should:

=SUM(SUMIFS(c2:c9,b2:b9,{">=6037","<=6332",4010},a2:a9,{100,101,134,136,141,355,357})) ---- How can I fix this???

	A	B	C
1	Dept	Acct	Amt
2	100	6000	10
3	200	6100	15
4	141	6107	20
5	100	6108	25
6	355	6109	30
7	141	6110	35
8	357	4010	40
9	100	5000	50

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[CyrusTheVirus]

Hey Ivan,

Please keep in mind, that it is always best to not include your starting point (how your data looks), but your ending point as well (how you want your data to look... or in other words... your expected results).

With that said, can you please clarify a few things?

1) What does the 4010 indicate within the formula? Are you saying that you want greater than or equal to 6037 AND less than or equal to 6332, but also include 4010 as well?
2) Why are you typing in these amounts {100,101,134,136,141,355,357} ? Are they not subject to change?

 

[IvanYerk]

Sorry about that, great suggestion and I'll definitely be more clear in future posts. The answer to both of your questions is YES.

 

[CyrusTheVirus]

no problem. Try out the below and let me know if that works for you.

=SUM(SUMIFS($C$2:$C$9,$B$2:$B$9,">=6037",$B$2:$B$9,"<=6332",$A$2:$A$9,{100,101,134,136,141,355,357})) + SUM(SUMIFS($C$2:$C$9,$B$2:$B$9,4010,$A$2:$A$9,{100,101,134,136,141,355,357}))

 

[IvanYerk]

Breaking it up did the trick! I have a habit of trying to skinny down the formula as small as possible and clearly sometimes it just can't and needs to be broken up into its parts.

Thanks for your help!!!! I really appreciate it!!!

 

[CyrusTheVirus]

You're welcome, glad to help.

Fyi, if you have a habit of doing that, then you might also have a habit of not using helper columns when creating spreadsheets... helper columns can be a huge 'help' (pun intended) when creating spreadsheets (makes formulas less complex, easier to alter, easier to read), so don't shy away from them. Best of luck, come back anytime.