Sum additional not adding all cell values

[ipbr21054]

Please see screenshot
The total shown in cell D31 is £30
As you can see in formula bar the code used is =SUM(D5:D30)

Wht isnt cell D31 showing the correct value

 

[etaf]

maybe some of the values are text and not numbers
usually a number is right justified and text left justified - but you may have centred

i see D5 is the starting range - but not sure what rows you are using

perhaps its not set to automatically calc and set to manual


Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[ipbr21054]

Start of cells is D5
End of cells is D30
Total is shown in cell D31

If i click in D31 Then the formula in the formula bar i then see a blue border appear around cells D5 to D30
This tells me all is ok.

All are formated as Currency & the value in the cell is centered as i have OCD

 

[etaf]

ok,
what value do you see in the bottom status bar , when you click on the column - it usually shows total , average etc , but will also include the Sum Value

see picture here

 

[ipbr21054]

Whilst waiting for a reply i did this.
My values that are placed in the column D cells are put there using an VLOOKUP for which the values are kept in column Q, make sense ?

So i retyped all the values agin,basically just going over what is already there.
I then started to enter some info on the sheet in question & now the sum is working correctly.

So just to advise.
I open a userform & enter a new customer & fees charged etc.
I then send these values to columns on my sheet at columns N to S & its where column Q holds the cost value, example £20.00

Here is the code that is used to send from userform to worksheet.
Do you see an issue.
Thanks

    Private Sub TransferValues_Click()
    Dim Lastrow        As Long, i As Long
    Dim wsGIncome      As Worksheet
    Dim arr(1 To 5)    As Variant
    Dim Prompt         As String
   
    Set wsGIncome = ThisWorkbook.Worksheets("G INCOME")
   
    For i = 1 To UBound(arr)
        arr(i) = Choose(i, TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value)
       
        If Len(arr(i)) = 0 Then
            MsgBox "YOU MUST COMPLETE ALL THE FIELDS", vbCritical, "USERFORM FIELDS EMPTY MESSAGE"
            Exit Sub
        End If
    Next i
   
    Application.ScreenUpdating = False
   
    With wsGIncome
        Lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row + 1
       
       With .Cells(Lastrow, 14).Resize(, UBound(arr))
            .Value = arr
            .Font.Name = "Calibri"
            .Font.Size = 11
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Borders.Weight = xlThin
            .Interior.ColorIndex = 6
           
            .Cells(1, 1).HorizontalAlignment = xlLeft
            Application.ErrorCheckingOptions.BackgroundChecking = False
       End With
           With Sheets("G INCOME")
           If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("N4:S" & x).Sort Key1:=Range("N4"), Order1:=xlAscending, Header:=xlGuess
           End With
         Unload ADDCUSTOMER
         .Range("N4").Select
       End With
       Application.ScreenUpdating = True
End Sub

It is TextBox4 that the value is entered & the code for this is shown here

    Private Sub TextBox4_AfterUpdate()
        TextBox4.Value = Format(TextBox4.Value, "£#,##0.00")
        TextBox4 = UCase(TextBox4)
    End Sub

 

[etaf]

sorry , i dont do VBA on the forums, someone else we need to help out here now