1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. Hi All

Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

Post Spam and you Will Be Deleted as a User

Hui...

3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

# only want to multiply certain cells

Discussion in 'Ask an Excel Question' started by Danin, Oct 10, 2017.

1. ### DaninNew Member

Messages:
27
I Have 2 sub total columns of different parts of an order. the totals get filled from other cells.
I only want to show the total of Subtotal column 1 multiplied by Rate column in NetTotal Column if both subtotal columns are full

I want to only ever multiply the rate by the first subtotal if both subtotal columns are filled.

Subtotal1 Subtotal2 Rate NetTotal

Subtotal1 x Rate = NetTotal
Subtotal2 x Rate = NetTotal
If Subtotal 1 and Subtotal 2 have values. I only want to use Subtotal 1 value x Rate = NetTotal.
2. ### NARAYANK991Excel Ninja

Messages:
16,619
Hi ,

You can have a formula such as :

=IF(AND(Subtotal1 <> "" , Subtotal2 <> "") , Subtotal1 x Rate , "")

Narayan
Thomas Kuriakose likes this.
3. ### DaninNew Member

Messages:
27
Thank you Narayan, I managed to get that far. It works well if both cells have a value in them. but what if only subtotal 2 has a value in it? I want it to calculate subtotal 2 x rate
subtotal 1 x rate
if both cells are filled. then it will only calculate subtotal 1.

I didn't explain the problem fully. my fault. I need that little extra that will say. ok. if a one of cells has a quantity in it, then multiply by rate.
if both cells have a quantity in it, then only multiply rate by subtotal 1.

if subtotal 1 does not equal zero, and subtotal 2 does not equal zero, only multiply subtotal 1 x rate. if subtotal 2 does not equal zero and subtotal 1 does equal zero, multiply subtotal 2 x rate. =IF(AND(\$H255<>"",\$L255<>""),\$H255*\$N255,"")*IF(AND(\$L255<>"",\$H255<>FALSE),\$L255*\$N255,"")

?Didn't work. i tried
4. ### NARAYANK991Excel Ninja

Messages:
16,619
Hi ,

The construct of your IF statement should be :

=IF(AND(\$H255<>"",\$L255<>"") , \$H255*\$N255 , IF(\$L255<>"" , \$L255*\$N255 , ""))

If the highlighted portion is not satisfied , it means that either H255 or L255 or both are not having data in them.

In this case , the second IF statement is checking to see if L255 has a value in it ; if it has , then the result L255 * N255 is output.

If L255 does not have a value in it , it does not matter whether H255 has a value in it or not ; the output is a null string.

Narayan
Thomas Kuriakose likes this.
5. ### John Jairo VWell-Known Member

Messages:
478
Hi, to all!

Another option could be:
=IF(\$L255<>"",IF(\$H255<>"",\$H255,\$L255)*\$N255,"")

Blessings!
Thomas Kuriakose likes this.
6. ### DaninNew Member

Messages:
27
Thank you for your help. I am still not getting the answer I need. I can get the first part of the formula. I can't get work out how to add the last bit of the equation to the formula.

If H255 and L255 have numbers in them. ONLY use H255 * Rate. (yes formulas work)
IF only H255 has number in it and L255 is empty. than multiply H255*rate (yes formula works)
If only L255 has number in it and H255 is empty. multiply L255*rate. (No formula does not include this)

I can get the first 2 lines. but I can't get the last bit.
7. ### NARAYANK991Excel Ninja

Messages:
16,619
Hi ,

When the logic is explained clearly , the formula is easier to develop.

=IF(AND(\$H255<>"",\$L255<>"") , \$H255*\$N255 , IF(\$H255<>"" , \$H255*\$N255 , IF(\$L255<>"" , \$L255*\$N255 , "")))

Narayan
Thomas Kuriakose likes this.
8. ### DaninNew Member

Messages:
27
Thank you for your input unfortunatley the problem still exists.
the formula isn't multiplying L255*N255 if H255 is empty. your formula still doesn't allow for this part of equation.
maybe it isn't possible and i need to look at it a different way.
9. ### DaninNew Member

Messages:
27
I think the problem is the Bold part of formula. it is giving blanket statement to all of the formula that if H255 and or L255 is not empty than only use H255 calculation. so the formulas after won't use l255 even if H255 is empty.
10. ### DaninNew Member

Messages:
27
OMG I GOT IT. WOOHOO!!
Ok only the first Bold part needs fixing. i changed the cell. i have a VLOOKUP for different types of items. it is only one item that L255 will ever use for calculation.

=IF(AND(H255<>"",E255<>"LABOUR"), add rest of formula. it solves this one problem but i still need a blanket solution but this is urgent to get solved.
11. ### NARAYANK991Excel Ninja

Messages:
16,619
Hi ,

The AND function does not implement an and / or ; it implements an AND.

The highlighted portion of the formula will never be satisfied if either one of the cells is blank. Only if both cells have data in them will it be satisfied.

Thus , when we come to the further portions of the formula , the only possibilities are :

1. H255 is blank

2. L255 is blank

3. Both H255 and L255 are blank

The following formula caters to all of the above 3 conditions :

=IF(AND(\$H255<>"",\$L255<>"") , \$H255*\$N255 , IF(\$H255<>"" , \$H255*\$N255 , IF(\$L255<>"" , \$L255*\$N255 , "")))

If 1 is satsified , the output is L255 * N255

If 2 is satisfied , the output is H255 * N255

If 3 is satisfied , the output is blank.

In the absence of a workbook with data in it , there is nothing more I can say.

Narayan
12. ### DaninNew Member

Messages:
27
Thank you for all your help. from the above. I believe the equation is solved. would tick a box as solved if there was one
13. ### John Jairo VWell-Known Member

Messages:
478
Hi, again!

For your new condition, this could help too:

=IF(\$H255<>"",\$H255*\$N255,IF(\$L255<>"",\$L255*\$N255,""))

Blessings!
Thomas Kuriakose likes this.