SumIF to summarize amount in different sheets by date

T

Tanya

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

Max

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

Tanya

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
 
M

Max

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top