# Thread: Sumifs within a range

1. ## Sumifs within a range

Help!

In one column I need a sumifs formula that will add elements 330-334
In the next column I need a sumifs formula that will add elements 335-339

I have tried using =SUMIFS(\$M\$2:\$M\$11,\$D\$2:\$D\$11,"=>330*",\$D\$2:\$D\$11, "=<334",\$B\$2:\$B\$11,\$A16)
I have also tried =SUMIFS(\$M\$2:\$M\$11,\$D\$2:\$D\$11,"=>335*",\$D\$2:\$D\$11, "=<339",\$B\$2:\$B\$11,\$A15)

Both formulas gave me zero

Anybody no how to negotiate this?

Attachment of sample file: Sumifs within a range.xlsx

2. Oops. know not no

3. DG,

I think the problem is that you are comparing TEXT values vs Numbers in your criteria for the SumIfs.
Here's formulas that will work around the problem.
Grants: =SUMIFS(\$M\$2:\$M\$11,\$D\$2:\$D\$11,">229*",\$D\$2:\$D\$11," <334*",\$B\$2:\$B\$11,\$A15)
Intergv'tl: =SUMIFS(\$M\$2:\$M\$11,\$D\$2:\$D\$11,">334*",\$D\$2:\$D\$11," <339*",\$B\$2:\$B\$11,\$A15)

4. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

DGStewart (2012-09-19)

5. You also had an issue of using =< and => rather than >= and <=

=SUMIFS(\$M\$2:\$M\$11,\$D\$2:\$D\$11,">=330*",\$D\$2:\$D\$11, "<=334*",\$B\$2:\$B\$11,\$A15)

6. ## The Following User Says Thank You to rory For This Useful Post:

DGStewart (2012-09-19)

7. That worked perfectly but I don't understand why. I understand >229 but <334 would make me think that I wouldn't pull in any 334's. Additionally, >334 makes sense but <339 seems like I would miss the 339's. Of course when I changed a cell to test it computes just fine. Really thought I would need those "equal to" signs in there but I don't. Super happy this worked but maybe you could explain the why behind it?

8. Thanks for the tip. I added the = signs as you mentioned into RG's formula and the answer was good. Oddly enough, it didn't seem to matter whether they were present in the formula.

9. DG,

The = signs didn't matter because your sample data set did not contain the values on the edges of the range, i.e. 329. However if you do include those values in the data set the results will change incorrectly! Note on the other end it is 334* and w/o the * it will not include the 334! Remember when setting up formulas like this to use data to test the upper & lower range to make sure you have it coded correctly. Thus you should have values in your test data set for 333,334,335 and 329,330,331 to make sure only the correct ones are selected.

10. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

DGStewart (2012-09-19)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•