Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF to summarize amount in different sheets by date
I can't seem to figure this one out.
I have a sheet per check date and a summary sheet. In "check date" sheet, there are two columns, A=check amount & B= check mail date. There can be different amounts of rows per "check date" sheet. On summary sheet, I want the total checks mailed on by date. This will cross several "check date" sheets. I can do it using just one sheet, but I don't know how to add the other ranges. Please help. -- Tanya |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF to summarize amount in different sheets by date
One way I'd set it up, Tanya ..
Illustrated in this sample: http://www.freefilehosting.net/download/3b9jm Summarizing.xls Source "date" sheets are assumed named in this unambiguous manner, eg: 02Feb2008, 03Feb2008 In Summary, CheckMail dates assumed listed in A2 down List the source "date" sheets in D1 across. Enter it as real dates Place in D2: =SUMIF(INDIRECT("'"&TEXT(D$1,"ddmmmyyyy")&"'!B:B") ,$A2,INDIRECT("'"&TEXT(D$1,"ddmmmyyyy")&"'!A:A")) Copy D2 across/fill down as far as required. This easily extracts the total amounts for each checkmail date (in B2 down) from each source date sheet, and you know exactly what total amount is coming from which source date sheet. Then you could just simply sum it all up in B2 down with: =SUM(D2:IV2). Adapt/extend to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tanya" wrote: I can't seem to figure this one out. I have a sheet per check date and a summary sheet. In "check date" sheet, there are two columns, A=check amount & B= check mail date. There can be different amounts of rows per "check date" sheet. On summary sheet, I want the total checks mailed on by date. This will cross several "check date" sheets. I can do it using just one sheet, but I don't know how to add the other ranges. Please help. -- Tanya |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF to summarize amount in different sheets by date
I only want the mail date and total check amount on the summary page. Not
all of the columns. I am trying to keep it simple for the users. If we had to put a column for every check sheet on each month it would get to large. The check date sheets have other information on them also that is not used in the summary. And a check mail date could cover several date sheets. I put a sample of the sheet on here http://www.freefilehosting.net/download/3bahc Summarizing1.xls Thank you for your help Tanya "Max" wrote: One way I'd set it up, Tanya .. Illustrated in this sample: http://www.freefilehosting.net/download/3b9jm Summarizing.xls Source "date" sheets are assumed named in this unambiguous manner, eg: 02Feb2008, 03Feb2008 In Summary, CheckMail dates assumed listed in A2 down List the source "date" sheets in D1 across. Enter it as real dates Place in D2: =SUMIF(INDIRECT("'"&TEXT(D$1,"ddmmmyyyy")&"'!B:B") ,$A2,INDIRECT("'"&TEXT(D$1,"ddmmmyyyy")&"'!A:A")) Copy D2 across/fill down as far as required. This easily extracts the total amounts for each checkmail date (in B2 down) from each source date sheet, and you know exactly what total amount is coming from which source date sheet. Then you could just simply sum it all up in B2 down with: =SUM(D2:IV2). Adapt/extend to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tanya" wrote: I can't seem to figure this one out. I have a sheet per check date and a summary sheet. In "check date" sheet, there are two columns, A=check amount & B= check mail date. There can be different amounts of rows per "check date" sheet. On summary sheet, I want the total checks mailed on by date. This will cross several "check date" sheets. I can do it using just one sheet, but I don't know how to add the other ranges. Please help. -- Tanya |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF to summarize amount in different sheets by date
Here's an implemented sample, adapted to suit:
http://www.freefilehosting.net/download/3bail Summarizing_Tanya.xls With the "date" sheets' sheetnames listed in D1 across (Entered as real dates) In D2, copied across/fill down: =SUMIF(INDIRECT("'"&TEXT(D$1,"m-dd-yy")&"'!E:E"),$A2,INDIRECT("'"&TEXT(D$1,"m-dd-yy")&"'!D:D")) Then in B2, copied down: =SUM(D2:F2) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tanya" wrote: I only want the mail date and total check amount on the summary page. Not all of the columns. I am trying to keep it simple for the users. If we had to put a column for every check sheet on each month it would get to large. The check date sheets have other information on them also that is not used in the summary. And a check mail date could cover several date sheets. I put a sample of the sheet on here http://www.freefilehosting.net/download/3bahc Summarizing1.xls Thank you for your help Tanya |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF to summarize amount in different sheets by date
Try Pivot Table.
No formulas needed. Free chart. Columns D and E have been switched. http://www.freefilehosting.net/download/3bake |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What function do I use to summarize data levels on three sheets? | Excel Worksheet Functions | |||
How to retrieve last date and amount from columns? | Excel Worksheet Functions | |||
What is the Maximum amount of sheets in a workbook? | Excel Discussion (Misc queries) | |||
sumif-add amount to another cell based on two criteria | Excel Discussion (Misc queries) | |||
Charting series in different sheets (summarize) | Charts and Charting in Excel |