CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
doe
doe

Reputation: 57

Use cell value if others cells are empty

I have a column containing purchase price of items. I have 4 other columns containing current value of the item where only one of the 4 will contain a value. I would like to create a 5th column that uses the original purchase price value if there are no values in the other 4 columns for that row.

Example: for the item in row 2, if D2, E2, F2, and G2 are all empty, put B2 in H2, otherwise leave H2 blank.

Very new to spreadsheet formulas so I'm not quite sure where to start.

Thanks!

Upvotes: 0

Views: 62

Answers (2)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5509

=if(isblank(D2),if(isblank(E2),if(isblank(F2),if(isblank(G2),B2,""),""),""),"")

Basically it's a nested if statement that checks each cell value in order, so for example, if it is true that cell D2 is blank , it then checks to see if E2 is blank, followed by F2, then G2. If all of these return true , it will transfer B2's value. ELSE if false it leaves a blank string represented by ""

Upvotes: 2

doe
doe

Reputation: 57

Tinkered and got it.

=if(and(INDIRECT("J"&ROW())="", INDIRECT("K"&ROW())="", INDIRECT("L"&ROW())="", INDIRECT("M"&ROW())=""), INDIRECT("B"&ROW()), "")

Upvotes: 0

Related Questions