Adding values for selected years

[OSDavidL]

I am trying to add values from one column (H) if the date year in another (E) is 2006 or similar. I have tried the following with a 0 as the result (which is wrong).

={SUM(IF($E$7:$E$10070=(YEAR($E$7:$E$10070)=2006),$H$7:$H$10070))}

Any ideas as to what is wrong? I suspect the =(YEAR($E$7:$E$10070)=2006 section of the formula.

[Roger Govier]

Hi David

One way
=SUMPRODUCT(--(YEAR($E$7:$E$10070)=2006),$H$7:$H$10070)

--
Regards

Roger Govier


"OSDavidL" <[email protected]> wrote
in message news:[email protected]...
>
> I am trying to add values from one column (H) if the date year in
> another (E) is 2006 or similar. I have tried the following with a 0 as
> the result (which is wrong).
>
> ={SUM(IF($E$7:$E$10070=(YEAR($E$7:$E$10070)=2006),$H$7:$H$10070))}
>
> Any ideas as to what is wrong? I suspect the
> =(YEAR($E$7:$E$10070)=2006
> section of the formula.
>
>
> --
> OSDavidL
> ------------------------------------------------------------------------
> OSDavidL's Profile:
> http://www.excelforum.com/member.php...o&userid=31305
> View this thread:
> http://www.excelforum.com/showthread...hreadid=509961
>

[OSDavidL]

Hi Roger,

Many thanks this works.

Can I also add an AND function into the logical test? ie

=SUMPRODUCT(AND(($B$7:$B$10070)=1770,--(YEAR($E$7:$E$10070)=2006)),$H$7:$H$10070)

to look down another coloumn and verify if 2 items match before giving the answer? The above has a problem and doesn't want to work.

Regards

David

[Roger Govier]

Hi David

Yes you can, but not with that syntax. You don't need AND, you just have
another set of tests.
=SUMPRODUCT(--($B$7:$B$10070)=1770),--(YEAR($E$7:$E$10070)=2006),$H$7:$H$10070)The double unary minuses -- coerce the True's to 1's and False's to 0'sso they get multiplied together along with the values in column H.So1 x 1 x 100 will = 1000 x 1 x 100 will = 01 x 0 x 100 will = 0--RegardsRoger Govier"OSDavidL" <[email protected]> wrotein message news:[email protected]...>> Hi Roger,>> Many thanks this works.>> Can I also add an AND function into the logical test? ie>>=SUMPRODUCT(AND(($B$7:$B$10070)=1770,--(YEAR($E$7:$E$10070)=2006)),$H$7:$H$10070)>> to look down another coloumn and verify if 2 items match before giving> the answer? The above has a problem and doesn't want to work.>> Regards>> David>>> --> OSDavidL> ------------------------------------------------------------------------> OSDavidL's Profile:http://www.excelforum.com/member.php?action=getinfo&userid=31305> View this thread:http://www.excelforum.com/showthread.php?threadid=509961>

[OSDavidL]

Roger,

Thanks, it looks Ok but I get a "formula contains an error" message with the last H10070 highlighted. Your logic looks good but I can't see why it does not work. Any suggestions?

Rgs

David

[Roger Govier]

Hi David
My apologies.
Because I was copying and pasting from your formula with the AND in it,
I inadvertently left a stray ")" in there after the B range
It should be
=SUMPRODUCT(--($B$7:$B$10070=1770),
--(YEAR($E$7:$E$10070)=2006),
$H$7:$H$10070)

I have deliberately split the formula after the commas, to try to avoid
any funny wraps in posting.

--
Regards

Roger Govier


"OSDavidL" <[email protected]> wrote
in message news:[email protected]...
>
> Roger,
>
> Thanks, it looks Ok but I get a "formula contains an error" message
> with the last H10070 highlighted. Your logic looks good but I can't
> see
> why it does not work. Any suggestions?
>
> Rgs
>
> David
>
>
> --
> OSDavidL
> ------------------------------------------------------------------------
> OSDavidL's Profile:
> http://www.excelforum.com/member.php...o&userid=31305
> View this thread:
> http://www.excelforum.com/showthread...hreadid=509961
>

[OSDavidL]

Roger,

Many thanks, all sorted !

David