Adding a conditional

[Ed Mah]

I have to update some code written by another party. Original code

    ActiveCell.FormulaR1C1 = "=-SUMPRODUCT(--(LEFT('Data Sheet'!R2C2:R" & LastRowDS & "C2,10)=LEFT('Upload to Database'!RC1,10))," _
                             & "--(LEFT('Data Sheet'!R2C5:R" & LastRowDS & "C5,4)=LEFT('Upload to Database'!RC2,4))," _
                             & "--(LEFT('Data Sheet'!R2C9:R" & LastRowDS & "C9,4)=LEFT('Upload to Database'!RC3,4)), " _
                             & "--(LEFT('Data Sheet'!R2C24:R" & LastRowDS & "C24,4)=""2019"")," _
                             & "'Data Sheet'!R2C20:R" & LastRowDS & "C20)"

Want to add: or (LEFT('Data Sheet'!R2C24:R" & LastRowDS & "C24,5)=""18-19"") and tried but didn't work

ActiveCell.FormulaR1C1 = "=-SUMPRODUCT(--(LEFT('Data Sheet'!R2C2:R" & LastRowDS & "C2,10)=LEFT('Upload to Database'!RC1,10))," _
                             & "--(LEFT('Data Sheet'!R2C5:R" & LastRowDS & "C5,4)=LEFT('Upload to Database'!RC2,4))," _
                             & "--(LEFT('Data Sheet'!R2C9:R" & LastRowDS & "C9,4)=LEFT('Upload to Database'!RC3,4)), " _
                             & "--(LEFT('Data Sheet'!R2C24:R" & LastRowDS & "C24,4)=""2019"") or (LEFT('Data Sheet'!R2C24:R" & LastRowDS & "C24,5)=""18-19"")," _
                             & "'Data Sheet'!R2C20:R" & LastRowDS & "C20)"

How do I correct it?

 

[jorismoerings]

Hi,

Your missing criteria, like an IF function, to distinguish what text to add to the end result of the formula.
If there's no criteria and it's needs to be added to the formula change your OR part to &

 

[Ed Mah]

If I understand correctly the SUMPRODUCT acts similar to an IF statement. Each additional criteria, creates an smaller subset.

What I'm after is "any data = 2019 OR data = 18/19". I don't want "data = 2019 AND data =18/19"

 

[Ed Mah]

Please ignore. I understand what's happening now. Thanks.