Help with Date in SUMPRODUCT

[Jack Deuce]

Cols A has date of transaction, E contains the amount of transaction.
How can use SUMPRODUCT to get all interest in a defined year (yyyy)
from a summary worksheet. IOW, from the Index Page for 2010, find in
a defined worksheet ('My Checking') all interest that has been posted
so far in 2010? I have multiple bank accounts that pay interest and
would like to total interest paid to them on the Index Sheet. All
worksheets start in 2008 and post interest monthly or quarterly. The
Index Sheet has 2008, 2009, 2010, 2011....Need to know how to specify
the range for a entire year in the SUMPRODUCT function and YYYY as the
search criteria.

Thanks to all..

 

[Don Guillett Excel MVP]

Cols A has date of transaction, E contains the amount of transaction.
How can use SUMPRODUCT to get all interest in a defined year (yyyy)
from a summary worksheet.  IOW, from the Index Page for 2010, find in
a defined worksheet ('My Checking') all interest that has been posted
so far in 2010?  I have multiple bank accounts that pay interest and
would like to total interest paid to them on the Index Sheet.  All
worksheets start in 2008 and post interest monthly or quarterly.  The
Index Sheet has 2008, 2009, 2010, 2011....Need to know how to specify
the range for a entire year in the SUMPRODUCT function and YYYY as the
search criteria.

Thanks to all..

=sumproduct((year(a2:a22=2008))etc

 

[Harald Staff]

=sumproduct((year(a2:a22=2008))etc

Hi Don

Shouldn't that be
=sumproduct((year(a2:a22)=2008)etc

Best wishes Harald

 

[Don Guillett Excel MVP]

Hi Don

Shouldn't that be
=sumproduct((year(a2:a22)=2008)etc

Best wishes Harald

Yep. Haste makes waste
Thanks Harald

 

[Jack Deuce]

Need a little more help. My formula is showing 0. What am I doing
wrong?

=SUMPRODUCT(--((YEAR($A$5:$A$403)=2010)),($D$5:$D$403="INTEREST
PMT"),$E$5:$E$403) entered with CTL-Shift-Enter

ColA = Transaction Date, eg. 08/17/10
ColD = Transaction Desc, eg. Deposit, INTEREST PMT, ATM Withdrawal...
ColE = Transaction Amt

Thank you.

 

[Don Guillett Excel MVP]

Need a little more help.  My formula is showing 0.  What am I doing
wrong?

=SUMPRODUCT(--((YEAR($A$5:$A$403)=2010)),($D$5:$D$403="INTEREST
PMT"),$E$5:$E$403)    entered with CTL-Shift-Enter

ColA = Transaction Date, eg. 08/17/10
ColD = Transaction Desc, eg. Deposit, INTEREST PMT, ATM Withdrawal...
ColE = Transaction Amt

Thank you.





- Show quoted text -

=SUMPRODUCT((YEAR($A$5:$A$403)=2010))*($D$5:$D$403="INTEREST
PMT")*$E$5:$E$403) NO CTL-Shift-Enter
or try
=SUMPRODUCT(--(YEAR($A$5:$A$403)=2010)),--($D$5:$D$403="INTEREST
PMT"),$E$5:$E$403) NO CTL-Shift-Enter