I am trying to get the sum of multiple returns on a VLOOKUP. As an example:

Jan 24
Jan 57
Jan 239
Feb 72
Mar 16
Mar 44

I want to get a result that looks like:

Jan Feb Mar
320 72 60

I have tried:

=SUMPRODUCT(VLOOKUP((Jan,A2:B7,2,FALSE))

But unfortunatley that just returns 24.

I hope someone can help.

Alibo

Use SUMIF...

Assume D2:F2 = Jan, Feb, Mar (as TEXT entries)

Enter this formula in D3 and copty across to F3:

=SUMIF($A2:$A7,D2,$B2:$B7)

--
Biff
Microsoft Excel MVP


"Alibo" wrote in message
...
I am trying to get the sum of multiple returns on a VLOOKUP. As an example:

Jan 24
Jan 57
Jan 239
Feb 72
Mar 16
Mar 44

I want to get a result that looks like:

Jan Feb Mar
320 72 60

I have tried:

=SUMPRODUCT(VLOOKUP((Jan,A2:B7,2,FALSE))

But unfortunatley that just returns 24.

I hope someone can help.

Alibo

Alibo wrote:
I am trying to get the sum of multiple returns on a VLOOKUP. As an example:

Jan 24
Jan 57
Jan 239
Feb 72
Mar 16
Mar 44

I want to get a result that looks like:

Jan Feb Mar
320 72 60

I have tried:

=SUMPRODUCT(VLOOKUP((Jan,A2:B7,2,FALSE))

But unfortunatley that just returns 24.

I hope someone can help.

Hi Alibo,

VLOOKUP is best to return a result from only the first row where the
criteria fit, hence your result.

For summary data, lay out "Jan Feb Mar" in D1:F1. In D2 put

=SUMIF($A2:$A7,D2,$B2:$B7)

and fill right.

Then there's the pivot table option...

Hi,

You really should use SUMIF but here is the correct SUMPRODUCT formula

=SUMPRODUCT(($A2:$A14=D1)*$B2:$B14)

Where Jan is in D1 and you want to copy from left to right.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Alibo" wrote:

I am trying to get the sum of multiple returns on a VLOOKUP. As an example:

Jan 24
Jan 57
Jan 239
Feb 72
Mar 16
Mar 44

I want to get a result that looks like:

Jan Feb Mar
320 72 60

I have tried:

=SUMPRODUCT(VLOOKUP((Jan,A2:B7,2,FALSE))

But unfortunatley that just returns 24.

I hope someone can help.

Alibo

Hi

VLOOKUP will return the first match
it found. To perform summing a range
with a criteria, there are a couple of
ways doing it.
Here one way :
=SUM(IF($A$2:$A$7=C1,$B$2:$B$7,0))
where C1 is the cell you enter Jan

Pls note that this an array formula,
which you must confirm by Ctrl,Shift and Enter
together. There is a curly bracket {...}
around the formula

If your data os sort as in your example,
another easlier way is to use the SubTotal
in Excel via Data pin the menu bar,
Select Subtotals
Choose Date then Sum Checked col B

HTH
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis









"Alibo" wrote:

I am trying to get the sum of multiple returns on a VLOOKUP. As an example:

Jan 24
Jan 57
Jan 239
Feb 72
Mar 16
Mar 44

I want to get a result that looks like:

Jan Feb Mar
320 72 60

I have tried:

=SUMPRODUCT(VLOOKUP((Jan,A2:B7,2,FALSE))

But unfortunatley that just returns 24.

I hope someone can help.

Alibo