SUMPRODUCT Problem

[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 D44650 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"),(D44650))

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

TIA for your help
Joe

 

[JE McGimpsey]

Try:


=SUMPRODUCT(--(YEAR(B4:B4650)=2006),--(E4:E4650<>"Rejected"),D44650)

 

[Guest]

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

 

[T. Valko]

Try this:

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

Biff

 

[Guest]

Worked perfectly. Thank you for the help.

Try this:

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

Biff

 

[T. Valko]

You're welcome. Thanks for the quick feedback!

Biff