Summary - Variable Ranges

[Lindsay0385]

Hi - I probably didn't title this right, but I'm not sure how to explain this. I'm working on a spreadsheet similar to this (this is a very basic example), with a summary at the bottom for all items:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Year 1[/TD]
[TD]Year 2[/TD]
[TD]Year 3[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]$20,000[/TD]
[TD]$30,000[/TD]
[TD]$40,000[/TD]
[/TR]
[TR]
[TD]Units[/TD]
[TD]1,000[/TD]
[TD]1,500[/TD]
[TD]2,000[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]$20,000[/TD]
[TD]$30,000[/TD]
[TD]$40,000[/TD]
[/TR]
[TR]
[TD]Units[/TD]
[TD]1,000[/TD]
[TD]1,500[/TD]
[TD]2,000[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]$20,000[/TD]
[TD]$30,000[/TD]
[TD]$40,000[/TD]
[/TR]
[TR]
[TD]Units[/TD]
[TD]1,000[/TD]
[TD]1,500[/TD]
[TD]2,000[/TD]
[/TR]
[TR]
[TD].[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD].[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD].[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Summary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]$60,000[/TD]
[TD]$90,000[/TD]
[TD]$120,000[/TD]
[/TR]
[TR]
[TD]Units[/TD]
[TD]3,000[/TD]
[TD]4,500[/TD]
[TD]6,000[/TD]
[/TR]
</tbody>[/TABLE]

What I need help with is that when someone uses the spreadsheet there could be 1 item or 20 items, each time it will be different (signified by the "..." above).

The spreadsheet starts with 1 item and the person using it presses a button to run a macro to add additional items until they've added enough for how many items they need and then they fill out the values for each items. Is it possible to build the summary to account for this?

I was just going to leave a blank summary at the bottom and the person filling out the form would have to sum up everything manually, but I figured I would check here first to see if anyone has come across a situation like this.

Thanks,
Lindsay

 

[Fluff]

You should be able to use a formula like
=SUMIF(A2:A11,"Sales",B2:B11) and
=SUMIF(A2:A11,"Units",B2:B11)

When the new rows are inserted the formula should account for it.

 

[Lindsay0385]

You should be able to use a formula like
=SUMIF(A2:A11,"Sales",B2:B11) and
=SUMIF(A2:A11,"Units",B2:B11)

When the new rows are inserted the formula should account for it.

Awesome! I had no idea it would be that easy! Thanks so much!

 

[Fluff]

You're welcome