Re: DSUM non-contiguous criteria

Tech-Archive recommends: Fix windows errors by optimizing your registry

Try using a data table with an extra column and row which you can hide.
Assuming the data in the example is in the range A11:E13...

a) Insert a column in column E and enter an index next to the data:

E
12 | 1
13 | 2
14 | 3 ...

b) Insert a line below the titles and enter in A12:E12 (from the
right):

12 | =OFFSET(A12,E12,) =OFFSET(B12,E12,) =OFFSET(C12,E12,)
=OFFSET(D12,E12,) 1

c) Select E12:F15 and choose Data > Table > Column input cell E12:

E F
12 | 1 =DSUM(stocklist,"Qty",A11:D12)
13 | 1 =TABLE(,E12)
14 | 2 =TABLE(,E12)
15 | 3 =TABLE(,E12)


RD Wirr wrote:

Does anyone have a way to have non-contiguous criteria for a DSUM function? I
have a work*** with a range of data that I need to run many similar queries
on but with one single changing criteria item. so I'd like to have a row of
field headings at the top and just copy down the criteria variables below and
with the actual DSUM function for each query in each copied row. Something
like this:
Item StartDate EndDate Location Qty
Item1 >=38517 <=39067 Loc1 =DSUM(StockList,"Qty",A11:D12)
item2 >=38517 <=39067 Loc1 =DSUM(StockList,"Qty",(A11:D11,A13:D13))

I know this formula doesn't work but does anyone have a way to do this?
The problem is, I have lots of rows and these query results are used in
formulas on another *** and if I have to include the column heading above
each criteria, it means hand linking each cell.
Thanks in advance,
RDW

.

Quantcast