Sum within a IF formula using Excel.

Microsoft Microsoft excel 2007 full vers...
March 29, 2010 at 02:25:40
Specs: Windows XP
Hello, I have been trying to add a sum into my if statement, for my college coursework. Is there anyway around this? I have a tick box and when unticked it results in false (n/a) on my sheet. However when i tick it, it results in 15 (the price for additional cost). I need to multiply the 15 by 1,2,3 though.. I have tried puting "E5*F4" etc but it doesnt work. any help?

See More: Sum within a IF formula using Excel.

Report •

March 29, 2010 at 04:24:23

As you haven't said which cell contains either 15 or the NA error value, I can only give you some general guidance:

If cell E5 contains either the value 15 or the error value #N/A
and if F4 to F13 contain the numbers 1 to 10, then enter this formula in G4:


The $ signs in the formula allow you to drag the formula down in column G alongside the values in column F, to return values for all the numbers 1 through 10.

The formula first tests if cell E5 contains the NA error value. If it does contain the error value it multiplies the relevant number by 1. If there is no error in cell E5, it multiplies the relevant number by the value in E5.

If you have just linked the tick box value to cell E5, cell E5 will show either TRUE or FALSE. This formula in cell G4 will work:


This formula tests the logical value in cell E5. IF($E$5 is either TRUE or FALSE. You could use IF($E$5=TRUE and get the same result, but it is not necessary. Note that TRUE and FALSE are Excel logical values and are not text.

It would also be useful to work through why there is an error value in cell E5. You mention a tick box - if the code attached to the tick box (check box) is this:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
    ActiveSheet.Range("E5") = 15
    ActiveSheet.Range("E5") = 1
    End If
End Sub

Then E5 will contain either 1 or 15 and the formula in G4 is just:

If you were trying to add 15% to a value then use the tick box code to return 15 or 0 and in cell G4 enter this:



Report •
Related Solutions

Ask Question