Formula to calc YTD sales by product/region/month/year?

[Tiffany R]

Hello!

I'm trying to devise a formula to calculate YTD sales for a product b
region/month/year. Columns are:

Year-Month-region-product name-sales dollars-YTD sales dollars.

The data ranges across 4 years, 12 months, 10 regions, 7 products.

So, in each row the YTD cell would have to

1.Look in other rows to find product matches (text),

2. then look for a match by year and region.

3. Then it would have to identify whether the month in each row wa
less than the month in the row of the current cell.

4. and if so, add sales dollars for all those months less than th
current row's month with the row's own sales dollars to the YTD cell.
Whew!

Then--as if that wasn't enough--I need basically the same formula, bu
that will sum sales numbers for the previous year (so the problem i
added that it will have to search for a month number sequence acros
years?)...

But I can't figure out how to make this kind of formula work! I'v
tried some if's and sumif's and the conditional wizard, but no go. I'
fairly new to Excel and this is apparently way over my head. Any hel
would be truly appreciated--if clarification is needed, let me know!

Thanks so much!
Tiffan

 

[Myrna Larson]

If all rows are as you show, you should be able to use a Pivot Table for this.

 

[Tiffany R]

Thanks for your reply! Yes, we can do it in a Pivot Table, but have t
add a calculated item for a new month each time we update the sale
info. We're trying to cut down on updating steps by having th
spreadsheet do all the calculations, because we run a lot of Pivo
Tables/Charts off the same info.

Is this not workable