Addition of hidden values from 4 columns

0
votes

I have to make a system in excel wherein if I put ‘yes’ against three columns out of four columns then the total(hidden cost) must be calculated only on the basis of three columns. Now I also have to put some numerical values which will be the cost of the respective column but this should be hidden.How to hide values Example if I have hidden range say 10 in col 1,15 in col 2,30 in col 3 and 20 in col 4 then if I put ‘yes’ in col 1 col 2 col 3 and ‘no’ in col 4 then cost must come out to be 55. I am a beginner and struggling to work on it.Please advice.

1
excelexcel-formula
Why don't you hide columns 1,3,5,and 7 and put YES in columns 2,4,6, and 8 – Gordon
Ok thanks,even this is better but how to add total cost when only three have yes and one has no in it. – richa1465

1 Answers

0
votes

have a look at the screenshot and imagine that the gray columns are hidden. To keep things simple the formula that checks for yes has been split accross 4 columns that will also be hidden =IF(B1="YES",A1,0) =IF(D1="YES",C1,0) etc. The formula in I1 is a sum of those 4 hidden columns =SUM(J1:M1)

Screenshot

Simpler version

This version puts all the hidden columns together and the sum can be calculated using a single formula.

Screenshot 2