combined HLOOKUP (urgent for a friend)

T

tom ossieur

Hi!

given the table

2007 2008 2008 2009 2012
10% 20% 40% 35% 10%

I want to calculate the sum of the values in row 2 for a certain year, e.g.
2008. How to do if a certain value is shown more than once?

e.g.
if given 2008,
result = 60%

HLOOKUP returns only one value (the same applies to the MATCH function)

any solution???


thanks!

tom
 
T

tim m

How about using SUMIF?
=SUMIF(A1:E1,"=2008",A2:E2)
It will look at all the year cells and if they are =2008 then it will sum
them.
 
J

jiang

Thanks for your reply, Tim.

I suppose I didn't express my question fully. Let me reword my question:

given the table

Sep-2007 Mar-2008 Sep-2008 Mar-2009
10% 20% 40% 35%

I want to calculate the sum of the values in row 2 for a certain year, e.g.
2008, and then put it in a separate table with heading only showing years,

2007 2008 2009
10% 60% 35%

Any function can solve this?
 
D

daddylonglegs

Assuming your first table in A1:D2 and second table in A4:C5 (i.e. years
in A4:C4)

formula in A5 copied across

=SUMPRODUCT(--(YEAR($A1:$D1)=A4),$A2:$D2)
 
J

jiang

thanks. what's the function of "--" I can't seem to locate it from the Excel
help file.

if I do not use "--", but instead using
SUMPRODUCT((YEAR($A$1:$D$1)=A4),$A2:$D2) directly, the result comes out as 0,
why?
 
D

daddylonglegs

The sumproduct formula, as the name implies, multiplies arrays of
numbers and then sums the resultant array - but it needs to work with
numbers.

The (YEAR($A$1:$D$1)=A4) part of the formula produces an array of
TRUE/FALSE values, e.g. something like {TRUE,TRUE,FALSE,FALSE} so for
the formula to work as desired these need to be "co-erced" to 1/0
values. The -- (known as double unary minus) converts the above array
to {1,1,0,0}

There are other "co-ercers" that can be used, essentially any
mathematical operation that won't change the value, e.g. +0 or *1,
e.g.

=SUMPRODUCT((YEAR($A1:$D1)=A4)+0,$A2:$D2)

......or you can use a slightly different formulation....

=SUMPRODUCT((YEAR($A1:$D1)=A4)*($A2:$D2))
 
J

jiang

many thanks for your kind reply. It's very helpful to know the co-ercing
function :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top