SOLVED

# Sumproduct with three criteria, one of which is an or statement with two dates

Copper Contributor

# Sumproduct with three criteria, one of which is an or statement with two dates

Hi everyone,

This is a complex endeavor for me. I am attempting to use sumproduct with three criteria on another tab.

Criteria 1: If column C indicates "YES"

Criteria 2: If column F indicates "Standalone Service"

Criteria 3: If either columns AA or AB have August in the date

Here's the formula I'm trying to work with

=SUMPRODUCT((Master_Calendar!\$F\$3:\$F\$503="Standalone Service")*(Master_Calendar!\$C\$3:\$C503="YES")*((MONTH(Master_Calendar!\$AA\$3:\$AA\$503)=8)+(MONTH(Master_Calendar!\$AB\$3:\$AB\$503)=8)))

I don't know what I'm doing wrong

Thanks

6 Replies

# Re: Sumproduct with three criteria, one of which is an or statement with two dates

@crb3209
There's a small discrepancy in your formula:
Master_Calendar!\$C\$3:\$C503 >>> Master_Calendar!\$C\$3:\$C\$503

# Re: Sumproduct with three criteria, one of which is an or statement with two dates

Thank you, Rodrigo. I think I've been staring at it too long. I fixed it, but it still doesn't work.
best response confirmed by crb3209 (Copper Contributor)
Solution

# Re: Sumproduct with three criteria, one of which is an or statement with two dates

use this formula,
=SUMPRODUCT((Master_Calendar!\$C\$3:\$C\$503="YES")*(Master_Calendar!\$F\$3:\$F\$503="Standalone Service")*(((MONTH(Master_Calendar!\$AA\$3:\$AA\$503)=8)+(MONTH(Master_Calendar!\$AB\$3:\$AB\$503)=8)) > 0))

# Re: Sumproduct with three criteria, one of which is an or statement with two dates

It works!!!! Thank you so much. What does the ">0" do?

# Re: Sumproduct with three criteria, one of which is an or statement with two dates

It just ensuring that the logical condition for either of the dates being in August is evaluated within the sumproduct, contributing to the final count only when the condition is met.

# Re: Sumproduct with three criteria, one of which is an or statement with two dates

Got it! Thank you
1 best response

Accepted Solutions
best response confirmed by crb3209 (Copper Contributor)
Solution

# Re: Sumproduct with three criteria, one of which is an or statement with two dates

use this formula,
=SUMPRODUCT((Master_Calendar!\$C\$3:\$C\$503="YES")*(Master_Calendar!\$F\$3:\$F\$503="Standalone Service")*(((MONTH(Master_Calendar!\$AA\$3:\$AA\$503)=8)+(MONTH(Master_Calendar!\$AB\$3:\$AB\$503)=8)) > 0))