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)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=566080

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=566080

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