conditional sum query
 

Hi there,

I have a table of data as follows:
Verified Date Value
01-Nov-06 26-Oct-06 3
01-Nov-06 31-Oct-06 3
01-Nov-06 31-Oct-06 10
03-Nov-06 02-Nov-06 2

I now need to set a formula that will test a couple conditions and then
return the sum of the true values

The condition is as follows:

Date equals my query date
if Date is a Monday to Thursday & Verified minus Date is less than 2 days or
if Date is a Friday or Saturday & Verified minus Date is less than 4 days or
if Date is a Sunday & Verified minus Date is less than 3 days
then sum the values in Value

I was able to do this when I was counting the true values only by using an
array on Count and If functions however I am now having difficulties in
rewriting this to sum.

Any tips please?

conditional sum query
 

=SUM(IF(A2:A20-B2:B20<INDEX({2,4,4,4,4,3,3},MATCH(WEEKDAY(B2:B20) ,{1,2,3,4,5
,6,7},0)),C2:C20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"dmorris1" wrote in message
...
Hi there,

I have a table of data as follows:
Verified Date Value
01-Nov-06 26-Oct-06 3
01-Nov-06 31-Oct-06 3
01-Nov-06 31-Oct-06 10
03-Nov-06 02-Nov-06 2

I now need to set a formula that will test a couple conditions and then
return the sum of the true values

The condition is as follows:

Date equals my query date
if Date is a Monday to Thursday & Verified minus Date is less than 2 days
or
if Date is a Friday or Saturday & Verified minus Date is less than 4 days
or
if Date is a Sunday & Verified minus Date is less than 3 days
then sum the values in Value

I was able to do this when I was counting the true values only by using an
array on Count and If functions however I am now having difficulties in
rewriting this to sum.

Any tips please?