If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Subtotals from Named Range on other worksheet
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? |
#2
|
|||
|
|||
Subtotals from Named Range on other worksheet
sgodschalk wrote:
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/xlFunctio...tml#SumProduct |
#3
|
|||
|
|||
Subtotals from Named Range on other worksheet
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" wrote: sgodschalk wrote: 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/xlFunctio...tml#SumProduct |
#4
|
|||
|
|||
Subtotals from Named Range on other worksheet
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 wrote: 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" wrote: sgodschalk wrote: 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/xlFunctio...tml#SumProduct |
#5
|
|||
|
|||
Subtotals from Named Range on other worksheet
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" wrote: 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 wrote: 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" wrote: sgodschalk wrote: 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/xlFunctio...tml#SumProduct |
#6
|
|||
|
|||
Subtotals from Named Range on other worksheet
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) sgodschalk wrote: 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" wrote: 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 wrote: 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" wrote: sgodschalk wrote: 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/xlFunctio...tml#SumProduct |
Thread Tools | |
Display Modes | |
|
|