Multiple Conditions in IF Formula for Excel

I am attempting to have my sheet compare the prices of two competitors products with shipping to be added in a separate column. Here's an idea of what we're doing:

Lowest Total Price | Competitor 1 Price | Competitor 1 Shipping | Competitor 2 Price | Competitor 2 Shipping


I will paste my formula below, which works perfectly only if we have two competitors filled in. If we only fill one in, and we leave the other blank, it ends up making the "Lowest Total Price" show 0.

My current attempt at a formula:

=IF(((F6+G6)<>0)<((J6+K6<>0)),F6+G6,J6+K6)


My previous attempt works the same, adding in the <> did not solve my issue, so here is my cleaner formula that works the same way...

=IF((F2+G2)<(J2+K2),F2+G2,J2+K2)


What can I do to ensure that this cell is not allowed to return 0 as a result?

0

=IF(AND(F6<>"",G6<>"",J6<>"",K6<>""),[your formula],"")

1 Answer
1

There are probably many ways to do this, but this is the way I found to do it...

Create a second sheet. In my case I named it "Background Data". This sheet will have three columns, as follows:

Competitor 1 Total | Competitor 2 Total | Lowest Competitor Total


In the first column, "Competitor 1 Total", I used the following formula:

=IF(Sheet1!F6<>0,Sheet1!F6+Sheet1!G6)


F6, in this case, is the "Competitor 1 Price" and G6 is "Competitor 1 Shipping". The formula is saying that if there is no price entered (thus if it is 0), then it will return FALSE as the total. If the price is anything other than 0, it will return F6+G6 (Competitor 1 Price + Competitor 1 Shipping).

The next column, "Competitor 2 Total", is going to use the following formula:

=IF(Sheet1!J6<>0,Sheet1!J6+Sheet1!K6)


This is doing the same exact thing as before, except it is using "Competitor 2 Price" and "Competitor 2 Shipping".

The final column in the "Background Data" sheet is going to be for the "Lowest Competitor Total". For this one, I simply used the MIN formula to determine the lower between the two. Since FALSE has no value, it simply returns the lowest real number. The formula is as follows:

=MIN(A6,B6)


The last step was to link the "Lowest Competitor Total" in the "Background Data" sheet to "Lowest Total Price" in my main sheet.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.