SUMPRODUCT with 3 conditions? 1

[Gymnast14]

After pouring through threads about Sumproduct, all of the examples seem to only have two conditions. Can you have three? Because I'm having a doozy of a time trying to get this formula to work:

'=SUMPRODUCT(('Turnover - Terms'!H2:H1249<=DATEVALUE("01/31/07"))*('Turnover - Terms'!Q2:Q1249="PCO")*('Turnover - Terms'!S2:S1249="VOLUNTARY"))

In real words: Count the records that have a term date less than 1/31/07 AND are dept PCO AND their term reason was voluntary.

For some reason, it does not like the third condition of S2:S1249="VOLUNTARY". When I take the third condition away, it works just fine. Any ideas?

 

[SkipVought]

Do you have and #xxx! error[/b] in column S?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[AnotherHiggins]

Try adding double unaries:
=SUMPRODUCT(--('Turnover - Terms'!H2:H1249 <= DATEVALUE("01/31/07")) * --('Turnover - Terms'!Q2:Q1249="PCO") * --('Turnover - Terms'!S2:S1249="VOLUNTARY"))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Hi John:

Using the -- I don't need to use the * sign I can use just , instead.

In any event, the way Gymnast14 has formulated it should still work.

As Skip has already pointed out, looks like there are some error values in the range S2:S1249 of OP's worksheet.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[Gymnast14]

I keep getting the #NA error. I think you guys are right, it's something wrong with my values in column S. I'll keep trying and let you know if I can get it to work.

 

[SkipVought]

HINT:

Use the AutoFilter on column S, to FIND the offending value.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[AnotherHiggins]

-> Using the -- I don't need to use the * sign I can use just , instead.

Yes, but * works with or without the --. Is there some processing speed advantage to using commas? If not, I'll just stick to always using the same thing.

->As Skip has already pointed out, looks like there are some error values in the range S2:S1249 of OP's worksheet.

Fair enough.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Gymnast14]

I figured it out. UGGGHH. How frustrating. One person was missing a term reason, and so my Vlookup formula was displaying #NA for her "Voluntary/Involuntary" designation. She was halfway down the list so I didn't even notice it. I hate it when it's something so STUPID like that!

Thanks for your help.