Subtotals from Named Range on other worksheet

  • Thread starter Thread starter sgodschalk
  • Start date Start date
S

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?
 
sgodschalk said:
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?
Click to expand...

Try SUMPRODUCT. See if this helps:

http://www.contextures.com/xlFunctions01.html#SumProduct
 
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)
 
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)
 
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.
 
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)
 
Back
Top