combined HLOOKUP (urgent for a friend)

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

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.


"tom ossieur" wrote:

> 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

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.


"tom ossieur" wrote:

> 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

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?

"tim m" wrote:

> 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.
>
>
> "tom ossieur" wrote:
>
> > 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

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)

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?


"tom ossieur" wrote:

> 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

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

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

"daddylonglegs" wrote:

>
> 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))
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=566080
>
>