Free Microsoft Excel 2013 Quick Reference

Not summing a columns?

When summing a column in Excel 2000. If additional numbers are added the
formula does not recalculate the amount. Unless I click on the formula in the
formula bar again and press enter. Then it works fine. Is there a way to make
it calculate automatically? It was working fine. Is there an option I turned
off somewhere?



I sum a column and want to use the same 'formula' for the next column so I thought you simply grab the handle and drag across to the next column and it would mimic the sum formula for the new column.

However it is copying the sum 'total' into the next column and not summing independantly the new column data.


can anyone tell me how to sum a column but if one of the cells is empty! the cell which should have sumed the numbers will display #N/A
1 7 1 7
2 2 2
3 4 3 4
4 13 4 #N/A
i.e because A2 cell data is missing cell A4 does not sum the column it displays #N/A

Please help -

Hello Excel gurus,

Ever since I found it on this site, I have been a huge fan of the code listed below, and I use it in almost every spreadsheet in one way or another. In the one I'm working on now, however, it seems that I need 3-4 of these ranges and "for each" sections to satisfy the macro.

Dim RngNext As Range 
Dim myRange As Range 
Set wsSheet = ActiveSheet 
With wsSheet 
    Set RngNext = .Range("a65536").End(xlUp).Offset(1, 0) 
End With 
Set myRange = Range("a3", RngNext) 
With cbocarrier 
    For Each RngNext In myRange 
        If RngNext.Value = cbogroup.Value Then .AddItem RngNext 
    Next RngNext 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My newest project is a sheet that lists payroll per class code per employee per week. Each employee earns money for different class codes every week. I am trying to pull this data to a second worksheet, which will list the Employees in column A and the Weeks across the rows. I want it to sum the total payroll for each employee by week (to do this, it must add the payroll for each code for that employee & week). If my explanation is confusing, see attached (template form only -- no data in attachment).

It seems like this would require me having to Dim 2 or 3 separate ranges and have several If and For Each statements, and I'm not nearly proficient enough with Excel to figure out which ones to do, and I'm not even sure that it's possible to do that many. And I'm sure it's not efficient at all.

Is there a better way to tell it to sum a column if 2 criteria are the true?


Hi folks, is there any formula that i can use to sum a column that contains #DIV/0 values?

A while ago I posted one for #N/A and got the following perfect solution from NBVC:


Im trying to use: =SUM(IF(NOT(ISERROR(C1:C6)),C1:C6)), but the result comes equal = 0.

Anyone know how to do it?

I need to sum a column. The column is 2500 rows, but only 200-400 have data in. It also needs to be an if-then type sum. The formula I am using is this:


This works, however the problem is that I have to type it all out by hand, which i would prefer not to do. Is there a simpler formula I can use to get this, or is there a way to do this in VBA? Thanks for any help you can give me.

I found something similar to what I wanted but not every 2nd row. So I thought I would modify it to fit want I am trying to accomplish and it did not work. So I am back to square one.
What I want to do is sum a column every time starting at row 6 as well as the next three columns ending with column 'H' and down to the last entry which will always vary from month to month. So here is what I have already and any help to get me past this will be GREATLY APPRECIATED!! I wanted to approach it from the angle of the code just entering the formula =SUM(E6:Exx) but I couldn't quite figure that one out either. When I2omani from another post was kinda in the same ballpark as me. I looked at those posts first.

Sub CloseMonth()
'Make final Entries into the month and subtotal out columns
    Do Until IsEmpty(ActiveCell) = True
        ActiveCell.Offset(1, 0).Select
        r = r + 1
    r = r + 2
    ActiveCell.Offset(r, 2).Select
    ActiveCell = "Delivery Services"
    ActiveCell.Offset(r, 5).Select
    ActiveCell = 24
    r = r + 1
    ActiveCell.Offset(r, -3).Select

'Enter a sum formula in the next 4 cells starting in row 6 and ending
'in row with Delivery Services as the customer name
    WorksheetFunction.Sum(Range("E" & r - r), Range("E" & r)).Value

End Sub

I am trying to sum a column.. series goes like this... $306, $200CR, $500...
I want to add the cells that do not end with CR.. how??

I need to sum a column until a preset value reached but not exceeded. The formulae the has to be copied down and apply to each row as it is.

For example:

Column A is listed numbers, in B they are summed, the "11" is the preset value, Column C is column B minus the preset value.

For the example please focus on Row 7 where we find: 6 28 17 2

First, "28"minus"11"(the preset value)=17 (column C)then reference back to column "B" where "15 is as far as you can sum without exceeding "17" itself. "2" is realized by "17"(column C) minus "15"(column B).

2 2 11
4 6
3 9
1 10
5 15 4 2
7 22 11 1
6 28 17 2
5 33 22 0
9 42 31 3

I am not near proficient enough myself to figure this one out, any help would be greatly appreciated


Can anyone tell me a formula to SUM a column to 30 then stop add a 1 to another column? Then start the count all over again, 365 days later. Keeping the same worksheet.

Hi -

I need help summing a column. What I want to do is to add a formula on the next empty cell and then add everything in that column.

So far here is the code I have:

Sub SumColumn()
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = ??
End Sub

Basically, it's a tally sheet, and there are lots of empty cells so I'm using Column F as my reference to find the next empty cell to sum column G. However, if the next cell is G14, I need to add everything up to G13. Is there a formula in which I can use to sum UP the column?

Any help will be most appreciated. Thanks!

I can record a macro to sum a column, but when I use this macro in the next worksheet, it seems to be hard wired for the number of data points from the original worksheet that the macro was recorded in. I tried using the relative button in both the on and off position, and am using the summation key at the top of the page.

Any ideas why?

What is the formula to sum a column of numbers, but exclude the TWO highest values in the column?



I have a excel spread sheet where I wish to sum a column based on the outcome of two other columns.

5 Normal High
10 Abnormal High
15 Normal Low

Now if I wish to sum those numbers in column A which are Normal (for B) and Low (For C) then how would I go about doing that. I was attempting to use an array formula but was having many issues. So far I have this...

=SUM(IF($M$16:$M$838="Normal", IF($R$16:$R$838="Interval 1", $N$16:$N$838,0),0))

But the number it gives me I know is wrong, it ends up summing everything in the column rather then the selected ones.

The variables are different but I hope the message got across.

I need to sum a column of numbers if it matches two different criteria.
I can set up the SUMIF easily for meeting one criteria, but I need to
also sum the column if it meets that criteria, and another. For
1 150 ABC MS1
2 200 DEF MS0
3 100 LMN MS0
4 125 ABC MS1
5 175 LMN MS1
6 225 DEF MS0

I need to have a formula that would say <<Sum column A IF column B =
"DEF" AND column C = "MS0">>. (and so forth for the other
I know there has to be a way to do this, probably using a combination
of an IF and SUMIF functions - but i keep getting #VALUE for answers.

Any help will be GREATLY appreciated.

Thanks so much -

i need to sum a column and put it into a variable.

any help would be appreciated.



I am trying to find a way to sum a column that contains a few cells w/ "#N/A" as the value. Is there an easy way to do this w/o the find/replace feature? Thanks

I'm trying to find a way to sum a column based on criteria in an adjacent column. In this case, I have a column that lists lengths of pipe with the location of the pipe listed in the adjacent column. I want to be able to sum up lengths based on the location criteria. My knowledge of Excel functions is fairly basic and I would appreciate any help.

I have a column that has the formula
=CHOOSE(MATCH(D42,{"ABS","Audi","VW","Back End Only"},0),350, 125, 125, 0)*F42
in each cell. I want to sum this column and it will not let me. The values in the cells are set to currency, but each cell without numeric values has N/A in it.
The sum function worked before I inserted this formula into the cells of the column.
Any help is apprecieated. Kat

Hello all!

I would like to use a SUMIFS statement to sum up a column of numbers based on two conditions. The first condition is a word. I have this done. The second condition is going to be a two week date range, which I am having problems solving.

Here is my formula that works so far.

Now I would like to SUMIF a second condition is met. The second condition is a date and time in this format.
1/9/2010 9:21:49 AM

I am only concerned about the date. Time does not matter. I cannot figure out how to specify a date range for the second condition. I am trying to specify between 1/1/2010 and 1/14/2010 as a test. So here is what I have so far with a SUMIFS.

=SUMIFS(Data!D:D,Data!C:C,"Office",Data!A:A,"Cannot figure out how to specify dates")

Maybe my logic is wrong...maybe I need to do three conditions? Where column A has a date < 1/14/2010 and a condition that specifies a date > 1/1/2010.

All help is appreciated, let me know what you think. Oh and Data! is the worksheet in case that throws anyone off.

I know that the answer to this is really pretty simple. since what I am
trying to do is really so simple but I can't make it work. I wrote a if
formula that enters a value into a cell under specific sonditions and then I
want to sum that column of calculated values into one cell at the bottom of
that column and it won't work. If I manually enter a value in the cell in
the column it sums in the bottom cell. but a calculated value will not sum
into the cell. whats up.


I have a worksheet where I want to sum the contents of several columns via VBA. The given is that the raw data values are always pasted (beginning) in row 7; however, the number of rows (count of rows) in each case varies. For example, raw data is pasted (Values-only) in A7. Row 7 consists of column titles, so the data I want to sum begins in row 8. The objective is to write the VBA code that would create a subtotal in column B that can be copied and pasted to columns D, E & F. The wrinkle is that the number of rows of raw data will vary from case to case. In the specific example below, there are four rows of data.

I can get to the location where I want to input the total in column B with

[beg code]
ActiveCell.Offset(2, 0).Range("A1").Select
[end code]

This gets me to cell B13 or two cells below the last row of data in column B. I could enter a formula that simply sums B8:B12; copy the formula and paste to cells D13, E13 & F13; however, this "fixed reference" will not work in the next instance where I may have 5 rows or 8 rows of data. I can name the range of cells B8:B12 and sum by the range name, but copying the resulting formula and pasting to columns D, E & F results in an incorrect subtotal for those columns (i.e., the sum of column B is displayed in each column).

Any suggestions?

I have two files a.xlsx and b.xlsx. In b.xlsx, i need to retrieve the sum of a column G (ranging from rows 1 to 500) from sheet1 of a.xlsx, using sumifs, so as to satisfy a condition in column A of a.xlsx. Could you help me with the exact syntax? Thanks in advance.

Hello all , anyone can show me the Marco of sum a column (unknow limit; colud change every day ) and sum the total value always under the last row ?
i am a freshman of VBA , thanks for you help.

I'm trying to conditionally sum a column based on the outcome of matching numbers from two other columns.

The first column(Column A) has a limited list of numbers of accounts I need for a report(about 80 items). The second column is a list of account numbers generated off another computer system with very limited sorting ability(Has about 160 items) . The third column has the numbers(Dollar figures) associated with the account numbers in the 2nd column(also 160 items).

I cannot figure out how to write a formula that will look at the account number in cell A1, compare it to all the account numbers in column B and if it finds a match, add or output the number from column C which corresponds with(Is in the same row as) the number in column B. The output of the formula would be in column D.

I could see how to do this with a nested If statement, but I have too many rows to match up for that to work. I'm really stuck here, and I've searched for conditional summation threads and came up with nothing that was close enough to give me a start.

Any ideas?