Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check if date is between two dates, then sum only those rows
I have tab1 with begin date in column A, end date in column B. Basically they
are weeks, Mon-Sun (e.g. 01/04/2010 and 01/10/2010 in A/B resp.). Tab2 has data I'd like to count and sum. The transaction date is in column B. I want to sum tab2 column G (and a few other columns). Tab2 will be dynamic as I add records to it. Tab1 remains static with each week comprising one row. -- streetcar |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check if date is between two dates, then sum only those rows
The best tool for it is a pivot table. Put in your layaut col A & B in row
section, col G in Data section (double click to change it to sum - "sumarize by") Click yes if helped -- Greatly appreciated Eva "streetcar" wrote: I have tab1 with begin date in column A, end date in column B. Basically they are weeks, Mon-Sun (e.g. 01/04/2010 and 01/10/2010 in A/B resp.). Tab2 has data I'd like to count and sum. The transaction date is in column B. I want to sum tab2 column G (and a few other columns). Tab2 will be dynamic as I add records to it. Tab1 remains static with each week comprising one row. -- streetcar |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check if date is between two dates, then sum only those rows
In Sheet1,
you have startdate, enddate in A2:B2 down (these are assumed real dates) put in C2: =SUMPRODUCT((Sheet2!B$2:B$100=A2)*(Sheet2!B$2:B$1 00<=B2),Sheet2!G$2:G$100) Copy down. The above returns the sum of amounts in Sheet2's col G where dates in col B (in Sheet2, which must also be real dates) fall within the startdate till enddate specified in A2 & B2 (inclusive). Modify easily to suit. Arrive`? celebrate it, hit the YES below -- Max Singapore --- "streetcar" wrote: I have tab1 with begin date in column A, end date in column B. Basically they are weeks, Mon-Sun (e.g. 01/04/2010 and 01/10/2010 in A/B resp.). Tab2 has data I'd like to count and sum. The transaction date is in column B. I want to sum tab2 column G (and a few other columns). Tab2 will be dynamic as I add records to it. Tab1 remains static with each week comprising one row. -- streetcar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to check the date sequence in rows | Excel Discussion (Misc queries) | |||
Error Check Dates | Excel Discussion (Misc queries) | |||
How can I check whether a column of dates are all before today's d | Excel Worksheet Functions | |||
Count rows if date is between two specified dates | Excel Worksheet Functions | |||
Check column of dates against todays date | Excel Worksheet Functions |