Create Total based on lookup value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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)
 
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
 
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)
 
Thank you. It worked perfectly. I didn't know to press Ctrl + Shift + Enter
to tell Excel it was an array formula.

Kim
 
You don't need to with that formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top