Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Create Total based on lookup value
I have a spreadsheet that contains a column for the date (e.g. January 1,
2005) of transaction. In a seperate column I have a total dollar figure for the transaction. In a seperate sheet, I would like a lookup finding all transactions that took place in a particular month (e.g. January) with a total dollar figure for all transactions during that particular month. I have tested something like - =OFFSET(A16,MATCH("January",A17:A19, 0),1) but since the date column contains more information than just the month name (e.g. date and year) - a value isn't returned and I'm not sure how to create a running total. I did not have any luck trying to incorporate a wildcard (e.g. '*') but may be using the wrong syntax. Am I using the correct function or should I be using a VLookup? Any suggestions are greatly appreciated. Thank you! |
#2
|
|||
|
|||
If you just want the total, use
=SUMPRODUCT(--(TEXT('sheet name'!A2:A20,"mmmm")="January"),'sheet name'!B2:B20) -- HTH RP (remove nothere from the email address if mailing direct) "Kim" wrote in message ... I have a spreadsheet that contains a column for the date (e.g. January 1, 2005) of transaction. In a seperate column I have a total dollar figure for the transaction. In a seperate sheet, I would like a lookup finding all transactions that took place in a particular month (e.g. January) with a total dollar figure for all transactions during that particular month. I have tested something like - =OFFSET(A16,MATCH("January",A17:A19, 0),1) but since the date column contains more information than just the month name (e.g. date and year) - a value isn't returned and I'm not sure how to create a running total. I did not have any luck trying to incorporate a wildcard (e.g. '*') but may be using the wrong syntax. Am I using the correct function or should I be using a VLookup? Any suggestions are greatly appreciated. Thank you! |
#3
|
|||
|
|||
Thank you for your response. I am having trouble getting this to work.
Should I be using SUM instead of SUMPRODUCT? Also, is -- supposed to be an IF? Thank you for any additional assistance. Kim "Bob Phillips" wrote: If you just want the total, use =SUMPRODUCT(--(TEXT('sheet name'!A2:A20,"mmmm")="January"),'sheet name'!B2:B20) -- HTH RP (remove nothere from the email address if mailing direct) "Kim" wrote in message ... I have a spreadsheet that contains a column for the date (e.g. January 1, 2005) of transaction. In a seperate column I have a total dollar figure for the transaction. In a seperate sheet, I would like a lookup finding all transactions that took place in a particular month (e.g. January) with a total dollar figure for all transactions during that particular month. I have tested something like - =OFFSET(A16,MATCH("January",A17:A19, 0),1) but since the date column contains more information than just the month name (e.g. date and year) - a value isn't returned and I'm not sure how to create a running total. I did not have any luck trying to incorporate a wildcard (e.g. '*') but may be using the wrong syntax. Am I using the correct function or should I be using a VLookup? Any suggestions are greatly appreciated. Thank you! |
#4
|
|||
|
|||
No exactly as I gave it to you. Just watch for any wrap-around in the NG.
-- HTH RP (remove nothere from the email address if mailing direct) "Kim" wrote in message ... Thank you for your response. I am having trouble getting this to work. Should I be using SUM instead of SUMPRODUCT? Also, is -- supposed to be an IF? Thank you for any additional assistance. Kim "Bob Phillips" wrote: If you just want the total, use =SUMPRODUCT(--(TEXT('sheet name'!A2:A20,"mmmm")="January"),'sheet name'!B2:B20) -- HTH RP (remove nothere from the email address if mailing direct) "Kim" wrote in message ... I have a spreadsheet that contains a column for the date (e.g. January 1, 2005) of transaction. In a seperate column I have a total dollar figure for the transaction. In a seperate sheet, I would like a lookup finding all transactions that took place in a particular month (e.g. January) with a total dollar figure for all transactions during that particular month. I have tested something like - =OFFSET(A16,MATCH("January",A17:A19, 0),1) but since the date column contains more information than just the month name (e.g. date and year) - a value isn't returned and I'm not sure how to create a running total. I did not have any luck trying to incorporate a wildcard (e.g. '*') but may be using the wrong syntax. Am I using the correct function or should I be using a VLookup? Any suggestions are greatly appreciated. Thank you! |
#5
|
|||
|
|||
Thank you. It worked perfectly. I didn't know to press Ctrl + Shift + Enter
to tell Excel it was an array formula. Kim "Bob Phillips" wrote: No exactly as I gave it to you. Just watch for any wrap-around in the NG. -- HTH RP (remove nothere from the email address if mailing direct) "Kim" wrote in message ... Thank you for your response. I am having trouble getting this to work. Should I be using SUM instead of SUMPRODUCT? Also, is -- supposed to be an IF? Thank you for any additional assistance. Kim "Bob Phillips" wrote: If you just want the total, use =SUMPRODUCT(--(TEXT('sheet name'!A2:A20,"mmmm")="January"),'sheet name'!B2:B20) -- HTH RP (remove nothere from the email address if mailing direct) "Kim" wrote in message ... I have a spreadsheet that contains a column for the date (e.g. January 1, 2005) of transaction. In a seperate column I have a total dollar figure for the transaction. In a seperate sheet, I would like a lookup finding all transactions that took place in a particular month (e.g. January) with a total dollar figure for all transactions during that particular month. I have tested something like - =OFFSET(A16,MATCH("January",A17:A19, 0),1) but since the date column contains more information than just the month name (e.g. date and year) - a value isn't returned and I'm not sure how to create a running total. I did not have any luck trying to incorporate a wildcard (e.g. '*') but may be using the wrong syntax. Am I using the correct function or should I be using a VLookup? Any suggestions are greatly appreciated. Thank you! |
#6
|
|||
|
|||
You don't need to with that formula.
-- HTH RP (remove nothere from the email address if mailing direct) "Kim" wrote in message ... Thank you. It worked perfectly. I didn't know to press Ctrl + Shift + Enter to tell Excel it was an array formula. Kim "Bob Phillips" wrote: No exactly as I gave it to you. Just watch for any wrap-around in the NG. -- HTH RP (remove nothere from the email address if mailing direct) "Kim" wrote in message ... Thank you for your response. I am having trouble getting this to work. Should I be using SUM instead of SUMPRODUCT? Also, is -- supposed to be an IF? Thank you for any additional assistance. Kim "Bob Phillips" wrote: If you just want the total, use =SUMPRODUCT(--(TEXT('sheet name'!A2:A20,"mmmm")="January"),'sheet name'!B2:B20) -- HTH RP (remove nothere from the email address if mailing direct) "Kim" wrote in message ... I have a spreadsheet that contains a column for the date (e.g. January 1, 2005) of transaction. In a seperate column I have a total dollar figure for the transaction. In a seperate sheet, I would like a lookup finding all transactions that took place in a particular month (e.g. January) with a total dollar figure for all transactions during that particular month. I have tested something like - =OFFSET(A16,MATCH("January",A17:A19, 0),1) but since the date column contains more information than just the month name (e.g. date and year) - a value isn't returned and I'm not sure how to create a running total. I did not have any luck trying to incorporate a wildcard (e.g. '*') but may be using the wrong syntax. Am I using the correct function or should I be using a VLookup? Any suggestions are greatly appreciated. Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a lookup table with an added varable? | Excel Worksheet Functions | |||
I have cells with diffent color fonts and need to total based on . | Excel Discussion (Misc queries) | |||
Lookup with search range start based on position of last blank lin | Excel Worksheet Functions | |||
How do I create a total sheet that will pull info from all my she. | Excel Worksheet Functions | |||
Create a total based on multiple conditions is not giving correct. | Excel Worksheet Functions |