=sum skipping columns and date-dependent

**BRISBANEBOB** · 01-01-2009, 05:59 PM

Is it possible to write a forumula to total non-contiguous columns dependent on a date selected? I've attached a sample workbook of what I mean.

Grateful for any ideass. Thanks

**Ron Coderre** · 01-01-2009, 06:16 PM

Using your posted workbook.....try this

Please Login or Register  to view this content.

Copy that formula down through AQ7.

Is that something you can work with?

**BRISBANEBOB** · 01-01-2009, 06:24 PM

Hi Ron

Not really...the columns containing dates will contain the 'actual' figures, and at the end of each quarter are sub-totals of 'actuals' and 'budgets'. All I want to sum are the 'budget' figures =< that the data in B17

**Ron Coderre** · 01-01-2009, 06:36 PM

I'm a bit puzzled...the formula I posted DOES sum only the budget values.
Notice the offset of the columns in the formula (B:AL vs C:AM):

Please Login or Register  to view this content.

I attached an edited version of your workbook.
What am I missing?

**BRISBANEBOB** · 01-01-2009, 07:04 PM

Hi Ron

I can understand your confusion - it works perfectly in the sample but when I to my actual spreadsheet I get #VALUE. I've obviously done something wrong but I can't figure out what.

Thanks for your input - the power of SUMPRODUCT (if you know how to use it!) is awesome.

I'll keep on working on why my transfer doesn't work

**Ron Coderre** · 01-01-2009, 07:10 PM

If there are #VALUE! errors in the formula ranges, we could eliminate those and maybe solve the issue.

Try posting a sample workbook that more closely matches your working model.
We'll see what we can do.

**BRISBANEBOB** · 01-01-2009, 07:20 PM

Ron

The #VALUE occur when there is no data in the cells. I've eliminated it by using an If(error routine.

Would SUMPRODUCT work where I need to do a lookup as part of the formula? I've got a couple of thousand lines of data with a description next to each line. There are in total about 500 descriptions. If Iwant to SUM the values in the description lines into a twenty or thrity summaries, whould I use SUMPRODUCT? e.g. Descriptions "Line 1", "Line 2", "Line 3" and "Line 18" summarise into "Blue", descriptions "Line 34", "Line 37" and "Line 211" summarise into "Green". Does that make sense? The format is the same with non-contiguous columns.