sumproduct with date

[kmlynn1010]

Hello friends!

I can't figure out why this isn't working.

=SUMPRODUCT($N$9:$N$88<"10/1/04")*($E$9:$E$88="J Doe")

where I want it to count 1 and sum if the N cell is > 10/1/04 and the E cell has in it J Doe. When tested it doesn't pick it up. Does it have to do with the date? I've also tried to reference a cell in place of "10/1/04" but still no progress.

Any help is much appreciated!

 

[Aladin Akyurek]

Hello friends!

I can't figure out why this isn't working.

=SUMPRODUCT($N$9:$N$88<"10/1/04")*($E$9:$E$88="J Doe")

where I want it to count 1 and sum if the N cell is > 10/1/04 and the E cell has in it J Doe. When tested it doesn't pick it up. Does it have to do with the date? I've also tried to reference a cell in place of "10/1/04" but still no progress.

Any help is much appreciated!

10/1/04 with double quotes around is text, not a number.

Try:

=SUMPRODUCT(--ISNUMBER($N$9:$N$88),--($N$9:$N$88 < "10/1/04"+0),--($E$9:$E$88="J Doe"))

Change < to > in accordance with your text description if correct.

 

[kmlynn1010]

That makes sense with the quotes. However, even when I try:

=SUMPRODUCT($N$9:$N$88>W$6)*($N$9:$N$88<W$8)*($E$9:$E$88=U9)

it's notpicking it up. Thing is, I need to keep it simple. My co-workers don't get this stuff beyond the 4 basics, sum, sumif, countif, and if. So, it will be trashed if they can't get it. can we work with this above?

 

[kmlynn1010]

Sorry, that didn't make any sense. Here is the formula:

=SUMPRODUCT($N$9:$N$88<$V$7)*($E$9:$E$88="U9")

 

[Aladin Akyurek]

That makes sense with the quotes. However, even when I try:

=SUMPRODUCT($N$9:$N$88 > W$6)*($N$9:$N$88 < W$8)*($E$9:$E$88=U9)

it's notpicking it up. Thing is, I need to keep it simple. My co-workers don't get this stuff beyond the 4 basics, sum, sumif, countif, and if. So, it will be trashed if they can't get it. can we work with this above?

And: What is the trouble?

 

[kmlynn1010]

The formula simply will not recognize when it has met all the tests, and so, it is not producing a 1 or calculate what the result will be.

 

[Aladin Akyurek]

The formula simply will not recognize when it has met all the tests, and so, it is not producing a 1 or calculate what the result will be.

Could you just state what it returns and what you expect it to return?

 

[Brian from Maui]

Do you get a #VALUE error? You're missing a pair of () in the formula.

 

[Legacy 51064]

You need to change:

=SUMPRODUCT($N$9:$N$88<$V$7)*($E$9:$E$88="U9")

to:

=SUMPRODUCT(($N$9:$N$88<$V$7)*($E$9:$E$88="U9"))