Summing similar items

**Johnnyz** · 05-23-2012, 06:50 PM

Hi - I assume that this will require a macro if not please forgive a newbie.

I have an excel workbook that will contain about 20 worksheets. The 20 sheets are similar in layout but have different numbers of rows and different numbers of column repeats (see below).

Each spreadsheet has less than 1000 rows and 32 to 44 columns.

There are 3 columns that repeat between 4 and 8 times.

These 3 columns are: item, quantity (for each product) and total items per product (total items is calculated).

Each item may be in any one of the repeats.

I would like to be able to sum the total items per product of all items with the same name within a selected area and within the whole sheet. These sub-totals I'd like to keep on the sheet with the other information but at the side as a list of items and the sub total for each item.

I'm a newbie to excel and don't have a clue where to start to do this.

I'm sorry if this description isn't completely clear and that the attached part spreadsheet helps. Please ask me and I'll try to clarify specific points.
I'd greatly appreciate some help.

**jeffreybrown** · 05-23-2012, 06:55 PM

I don't know about Excel 2000 so I don't even know if this formula is available, but if so...

U4 =SUMIF($C$4:$O$21,S4,$E$4:$Q$21) copied down

if not then try

=SUMPRODUCT(--($C$4:$O$21=S4),$E$4:$Q$21)

**Johnnyz** · 05-24-2012, 06:47 AM

Hi thanks that's perfect.

I can now sum the whole sheet.

I've tried substituting =SUMIF(Selection,S4,$E$4:$Q$21) and some variations so that I can sum only a selected area but I just get an error #NAME

Is there an equally simple solution that will sum just a selected area?

Regards and thanks

**FDibbins** · 05-24-2012, 08:23 AM

so =sumif() IS available in 2000?

if so, in your formula, you use "selection" is that an actual named range in your workbook? if you dont have a range created with that name, that many be causing the error

**Johnnyz** · 05-24-2012, 10:56 AM

Yes sumif is in 2000 and no I haven't named a range selection I was trying to use the Selection property.

But thank you for the idea.

Naming the two ranges in the original formula Items and Totals. I end up with =SUMIF(Items,S4,Totals). It's rather clumsy having to manually name the two ranges but it works so if no one can improve on it I can live with it.

Thanks

**tigeravatar** · 05-24-2012, 11:27 AM

Johnnyz,

Have a look at dynamic named ranges. here's a couple links to get you started:
http://support.microsoft.com/kb/830287
http://www.contextures.com/xlNames01.html

**FDibbins** · 05-24-2012, 12:09 PM

i wasnt suggestion you use range names, just asking if you were trying to, but if they work for you thats great...i find its often easier (and sometimes makes more "readability") to use range names.

I didnt look at TA's dynamic ranges, but they can be very helpful too

**Johnnyz** · 05-25-2012, 08:13 AM

Hi FDibbins

The links are useful for my future reference but don't fit with my current problem. Looks Like I'll just do it the clunky way.

Thanks for your help.

**jeffreybrown** · 05-25-2012, 08:30 AM

Hi Johnnyz,

No need to do something the "clunky way" is there is an sound method available.

If you post a workbook (not a picture) of your current setup/layout and what you desire, maybe we can help spot a workable solution.