My apologies for leaving the thread for a couple of days there. I wasn’t at work and didn’t have the spreadsheet in front of me.
I do know that this is an awkward way to process the data, but it seems to be the best way to view and enter the data. The spreadsheet is for an audit, and the data needs to be all on one sheet in a clear and easy-to-read format. I’m doing something like this:
January 5 March 14 April 23 ...
QTY COST QTY COST QTY COST ...
ITEM1 3 6.00 2 4.04 4 7.92
ITEM2 12 48.48
ITEM3 1 19.93 1 19.96
--- ----- --- ----- --- -----
4 25.93 14 54.52 5 27.88
If I put the quantities and costs in separate tables, it would be hard to cross-reference. Summing down (total qty/cost for January 5 purchase) is easy. Summing across (total qty/cost for all item1’s purchased) is the pain.
I know this cries out for a database or 3-D spreadsheet approach, but there’s really not enough data to justify designing and building a custom database for it.
The dates aren’t in the individual rows (which kills K364’s solution).
I tried Palooka’s approach and can’t get it to work. If I put…
=SUM(IF(MOD(COLUMN(E8:AA8),2)=0,E8:AA8,0))
I get zero. If I change the =0 to an =1, I get the sum of all columns, odd or even.
For the moment, I went with the…
=F8+H8+J8+L8+N8+P8+R8+T8+V8+X8+Z8…
approach, but it requires modifying both formulas and copying down in two different tables (I’m doing this for two different product lines).
I’m still puzzled.