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!

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!

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!

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!

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!

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!