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

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

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

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

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