Ed Mah

New Member
Joined
Dec 13, 2003
Messages
22
I have to update some code written by another party. Original code


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


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"") or (LEFT('Data Sheet'!R2C24:R" & LastRowDS & "C24,5)=""18-19"")," _
                             & "'Data Sheet'!R2C20:R" & LastRowDS & "C20)"


How do I correct it?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
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 &
 
Last edited:

Ed Mah

New Member
Joined
Dec 13, 2003
Messages
22
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,109,306
Messages
5,527,921
Members
409,793
Latest member
mavrik_stet

This Week's Hot Topics

Top