combined HLOOKUP (urgent for a friend)

[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

 

[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.

 

[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?

 

[daddylonglegs]

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

formula in A5 copied across

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

 

[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?

 

[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))

 

[jiang]

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