Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting query | Excel Worksheet Functions | |||
Conditional MS Query | Excel Worksheet Functions | |||
AHHH! Again | Excel Discussion (Misc queries) | |||
AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
How to preserve conditional formatting on a web query table result | Excel Discussion (Misc queries) |