Sum financial values corresponding to range of accounts

**ernest t bass** · 05-08-2012, 09:28 PM

(this thread is re-posted from an earlier attempt with a non-descriptive title, at JB's request)

In Excel 2003 I'm trying to work with two columns of data. In one column are account numbers, the other is dollar values for the account.

I want a formula that will sum all data in the amount column for a given range of account numbers. Say from 600000-600199, from 600200-600299, etc. Of course I have more data than below. I have been trying to use >, <, in SUMIF arrays (no SUMIFS in '03).

Account Amount
600108 1,459
600109 16
600113 18,449
600201 0
600202 0
600203 0
600205 15,468
600206 581
600208 0
600209 0
600210 152
600212 987
600508 183
600702 0
601101 9,881
601104 11,357
601107 1,454

Any suggestions would be appreciated. Once this is worked out, I'll be using the solution for years.

PS, First post, new user. Hope I'm not asking something I could have easily found using search...

Ernest T

**Haseeb Avarakkan** · 05-08-2012, 09:41 PM

Hello Ernest, Welcome to the forum.

You can use SUMIF like,

=SUMIF(A:A,">=600000",B:B)-SUMIF(A:A,">600199",B:B)

See the attached.

**jeffreybrown** · 05-08-2012, 09:41 PM

Hi Ernest T,

Next time you can just edit the title in the original thread. Maybe we can get a moderator to merge these two threads.

First question, you say "600000-600199, from 600200-600299", but this spread in 200 and then 100. Is that just a typo or does it go from 000-199 and then every 100 after that?

If it is just a typo, would you be okay with a pivot table?

**ernest t bass** · 05-08-2012, 09:51 PM

Thanks again Jeffrey. The example wasn't intentional, but the range of accounts included will vary depending on the description of each, rather than the numerical range. A pivot table might work, but I'm trying to set up a template for monthly use. I hope to just be able to drop the data into a range and have the formulas return the values in their respective fields.

I am amazed at how quickly the responses are coming in. Regarding the old thread, I should have read the forum rules first, rather than try to edit and then repost. Lesson learned. If the original post can be deleted by the moderator it should be.

**ernest t bass** · 05-08-2012, 09:53 PM

Haseeb:

Thanks much. I like this solution and will try first thing in the office tomorrow. I'll let you know how it does, and will make sure to mark the thread as solved.

**jeffreybrown** · 05-08-2012, 09:56 PM

No problem Ernest.

As far as your values changing, a pivot table may not work well in this sitution.

If there was a natural split between the values, i.e., 000-099, 100-199, 200-299, etc, we could use a group on the pivot table, but it sounds like no so take a look at the attachment Haseeb A provided. I think this setup will meet your needs.

You may have to tweak it based on your needs, but if you get stuck, please post back.