SUMPRODUCT Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

First, thank you in advance for your help and looking at this.

I'm trying to use this formula to sum the values in cells D4:D4650 based on
the criteria that the date in cells B4:B4650 is from 2006 and it has not been
rejected (E4:E4650).
=SUMPRODUCT(--(B4:B4650=YEAR(2006)),--(E4:E4650<>"Rejected"),(D4:D4650))

The problem with this formula is that it returns $0.00 and it should be over
$5m.

TIA for your help
Joe
 
Your fix worked great thank you. I just need to learn how to write the order
of the formula.

I have one question though, if I may impose. Within the range of B4:B4650
there are numerous cells that have text, when I deleted the text the formula
worked but with the text it did not. Is there a way to leave the text and
still have it work?

Thank you again for your help.
Joe
 
Try this:

=SUMPRODUCT(--(TEXT(B4:B4650,"yyyy")="2006"),--(E4:E4650<>"Rejected"),D4:D4650)

Biff
 
Back
Top