ExcelBanter - SUMPRODUCT Problem

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - SUMPRODUCT Problem (https://www.excelbanter.com/new-users-excel/144417-sumproduct-problem.html)

SUMPRODUCT Problem

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

SUMPRODUCT Problem

Try:

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

In article ,
Joe Gieder wrote:

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

SUMPRODUCT Problem

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

"JE McGimpsey" wrote:

Try:

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

In article ,
Joe Gieder wrote:

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

SUMPRODUCT Problem

Try this:

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

Biff

"Joe Gieder" wrote in message
...
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

"JE McGimpsey" wrote:

Try:

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

In article ,
Joe Gieder wrote:

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

SUMPRODUCT Problem

Worked perfectly. Thank you for the help.

"T. Valko" wrote:

Try this:

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

Biff

"Joe Gieder" wrote in message
...
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

"JE McGimpsey" wrote:

Try:

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

In article ,
Joe Gieder wrote:

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

SUMPRODUCT Problem

You're welcome. Thanks for the quick feedback!

Biff

"Joe Gieder" wrote in message
...
Worked perfectly. Thank you for the help.

"T. Valko" wrote:

Try this:

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

Biff

"Joe Gieder" wrote in message
...
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

"JE McGimpsey" wrote:

Try:

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

In article ,
Joe Gieder wrote:

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