Subtotals from Named Range on other worksheet

[sgodschalk]

I have a worksheet that holds all of our daily transactions over the last 5
years. Been using just basic Pivots and charts until now. But I need I need
something more complex. The TransactionsDB range an sheet 1 holds a date in
the first column ie 6/1/2009. The breakdown of tender is in the next columns
ie "Cahs", Visa" so on and so forth.

In a separate worksheet, sheet 2, column A holds a date in a format of
6-2009. In Column B I need to query and subtotal all the "Cash" (column "C"
in the TransactionsDB range) transactions for the given month.

I have tinkered with Sumif, vlookup and all sorts of different formulas and
I cannot get it to work on my own...can you help?

 

[Glenn]

I have a worksheet that holds all of our daily transactions over the last 5
years. Been using just basic Pivots and charts until now. But I need I need
something more complex. The TransactionsDB range an sheet 1 holds a date in
the first column ie 6/1/2009. The breakdown of tender is in the next columns
ie "Cahs", Visa" so on and so forth.

In a separate worksheet, sheet 2, column A holds a date in a format of
6-2009. In Column B I need to query and subtotal all the "Cash" (column "C"
in the TransactionsDB range) transactions for the given month.

I have tinkered with Sumif, vlookup and all sorts of different formulas and
I cannot get it to work on my own...can you help?

Try SUMPRODUCT. See if this helps:

http://www.contextures.com/xlFunctions01.html#SumProduct

 

[sgodschalk]

That might be the ticket but I am having trouble comparing the date parts.
The actual date entered on sheet one are 6/1/2009, 6/2/2009 and so on. Where
as the date I am using for the subtotal sheet is 6-2009 (a custom date format)

 

[Glenn]

Use the MONTH() function to make the match.

=SUMPRODUCT((MONTH(A1)=MONTH(B2:B100))*C2:C100)

would total the cells in C2:C100 where the month in B2:B100 is the same as the
month in A1. If the data spans more than one year, try it this way:

=SUMPRODUCT(((A1-DAY(A1))=(B2:B100-DAY(B2:B100)))*C2:C100)

 

[sgodschalk]

Yes over 2000 rows of transactions spanning 5 years. So the second formula
would have to be utilized. Thanks for that.

Since the data is on a separate worksheet in a named range, can I reference
the range or do I use just the sheet name in the formula? Thanks again for
the help.

 

[Glenn]

You can use either. Just make sure that the ranges are all the same size. If
you are going to reference the sheet name, it would look like this:

=SUMPRODUCT(((A1-DAY(A1))=('Data Sheet'!B2:B100-DAY('Data Sheet'!B2:B100)))*
'Data Sheet'!C2:C100)