conditional sum query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dmorris1 - 07 Nov 2006 14:15 GMT

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?

Reply to this Message

Bob Phillips - 07 Nov 2006 15:39 GMT

=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.

Signature

HTH

Bob Phillips

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

> Hi there,
>
[quoted text clipped - 21 lines]
>
> Any tips please?

Reply to this Message