Forum Discussion

crb3209's avatar
crb3209
Copper Contributor
Aug 02, 2024

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

  • 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))
  • Rodrigo_'s avatar
    Rodrigo_
    Steel Contributor

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

    • crb3209's avatar
      crb3209
      Copper Contributor
      Thank you, Rodrigo. I think I've been staring at it too long. I fixed it, but it still doesn't work.
      • Rodrigo_'s avatar
        Rodrigo_
        Steel Contributor
        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))

Resources