Free Microsoft Excel 2013 Quick Reference

# formula to add up EVERY OTHER cell in a row

Can I set up a formula in an xls to add up EVERY OTHER cell in a row? And how
long can a string in a formula be? (are there limitations?)

I'm getting an error when I manually type in the list of cells to add up.

## Related Results

### Copying a Formula into Every other Cell In a Column

I have some formulas that I would like to copy into every other cell in a
given column .

Is this possible ?

### Make a formula that adds every 4th cell in a column indefinately?

In Office 2003 I need to make a formula that will add up every fourth cell in
a given column (i.e. A8,A12,A16,A20,etc). This range extends beyond 30
entries (one for each work day for a year) so entering each one individually
will not work. I haven't been able to find anything in the Excel help that
has been useful. Does anyone know how to accomplish this?

### Determine lowest number of every other column in a row

Hello,

I'm a pretty basic excel user and as the title says I need to determine the lowest number from every other column in a row.

My application is a burndown spreadsheet, see the example below. The columns with the 'w' are the work units dedicated to a task (row) in a given period of time and columns with 'r' indicate the work units remaining for a task after finishing a period of time.

..A B C D E F G H I J K L M N O
1 w r w r w r w r w r w r w r
2 5 3 1 3 1 2 1 1 1 0.........0
3 1 5 2 3 1 2.................2

What I'm trying to get are the numbers in Column 'O' which would be the remaining work for a task based on developers input not just 'x = work planned - work done'. So, taking into account the 'r' columns...Smallest number from A2:N2 is 0Smallest number from A3:N3 is 2
Note that empty cells are expected to be ignored.

Sounds like I should use 'SMALL' but I'm not sure how to specify it to use every other columns from a range. Can anyone help me out with the right formula to use?

### Formula to return last but one cell in row!

Hi all

I currently use the following formula to find the last used cell in a row:

=LOOKUP(2,1/(\$6:\$6<>""),\$6:\$6)

which works fine but I have been trying to amend it so that it returns the last but one cell in a row. Have tried using it with Offset but without success.

Have found other solutions to finding to the last cell in a row albeit that a number of them do not seem to work with my project and likewise none of them seemed to allow customisation of any sort.

Any suggestions ...spellbound

### How do i select every other cell in a column? every nth cell?

how can i select every other cell in a column? every nth cell? i have a
bunch of data but only want to to perform calculations on some of the points.

### How to count the no of cells in a row where the row no is derived from a calculation

Hi,

pls help i have searched the web but to no avail.

I am trying to count the number of cells in a row but this row is derived from a calculation.
my problem is how do i put in the variables so that there is no error??

```
VB:
Sheets("Temp").Range("A" & X) --  contains a number

Dim acc As var

acc = Sheets("Temp").Range("A" & X) - 99999
rowCount = Application.CountA(Sheets("Record").Rows(???))

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
help is greatly appreciated

### How to collect data from many cells in a row into one cell?

How to collect data from many cells in a row into one cell?

I provided and example, how I want it be:
A B C D E F G H I J K ... 1 Diameter List of elements ... 2 ∅6 9 1 3 5 9 1 3 5 ... 3 ∅8 5 4 7 9 5 4 7 9 ... ... ... ... ... ... ... ... ... ... ... ... ... ...
I have very long rows with many columns starting from column C, some cells are empty. In cell B2 I want to collect all data from cells of the row with single space between them. The same I want to do in cell B3 and downwards ...

I know in Excell 2010, the cell can contain 32767 characters maximum.
Maybe it's better to export that list of elements to text file or use aditional reserved cells C2; D2; E2 - if the text doesn't fit to B2.

Appreciate any help

### I need a way to sum every 2nd cell in a row

Hi there,

This is probably very easy to do, I've never had to do it before and I'm having difficulty getting it to work. So please bear with me and if someone has already explained this I'm sorry and if you could direct me to the correct thread that would be awesome.

I'm using Excel 2007 and I'm trying to make a sales tracking calendar, down the left side we have sales people listed, across the top dates. The dates take up two columns which will hold unit sales for item "A" and item "B". I want to be able to show totals for each.

I originally tried just selecting each individual cell and summing them...
=sum(f11,h11,j11,l11,n11 all the way to bw11)
This gave me the following error message "More arguments have been specified for this function than are allowed in the current file format." (I should mention that one month in each quarter has 5 weeks.)

So i did a little looking thru the help features and google trying to find a way to sum every 2nd cell in a range. I came across a site suggesting they had a way to do it so I modified it for my cells and all it seems to do is add all the cells together rather than every other...
{=SUM(IF(MOD(ROW(\$F\$10:\$BW\$10),2)=0,\$F\$10:\$BW\$10,0))}
I did not type in the {} i used the Ctrl-Shift-Enter like the site suggested.

Any suggestions would be greatly appreciated.

Many thanks

Kyle

### Formula to return every nth cell in a column

I want to return every other cell in a column, and return the remaining cells(also every other) in an adjacent column. I cannot have empty cells in between numbers due to the functions I am using.

For example,
A formula to take:
1
2
3
4
5
6

and return:
1 2
3 4
5 6

Don't know if this is possible but would appreciate any thoughts(can't use sort because the data refreshes to every other line from an outside source)

### Need formula or Function to sum value of every other cell in a col

Hello,

I have a need to sum the values in every other cell, and sometimes every
third cell, in a column or row.

Thanks
--
Brent

### Macro To Clear Contents In Every Other Cell Of A Selection

I need to clear the contents of every other cell in a selection of 5000 rows starting with the first cell. Is there a macro that can do this for me?

### Formula to select every third cell in a very long column of data?

Is there a formula or an easy way to select/highlight every third cell in a
very long column of data?
--
Thanks,
Jamie

### Questions on copying from one sheet to the other and coping every other cell.

I need to change a formula on sheet two reading...

=OFFSET(\$B\$1,0,ROW()-2)

which I drag down the column and it copies text like this

=B1
=C1
=D1
etc.

I need to make it every other cell in the row instead of every cell.

For instance now when I drag it, it will go down the column and copy
text from

=B1
=D1
=F1
+H1
Etc....

Second, first time ever but the columns in first row in sheet 2 is
full, so I also need to continue same formula on the next sheet, sheet
3 but have it continue copying to same column on Sheet 2. How do i make
this possible?

Thanks!

TKL

--
------------------------------------------------------------------------

Does anyone know of an elegant way to add every other cell in a row? I can do it manually (ex. a1+c1+e1+g1...etc.) but I'm hoping that there is a specific command for this action; it would be a real time-saver.

Thanks!

### Need formula to calculate how many cells show a specific word

How can I create a formula to add up how many cells in a column show a particular set of text? For instance; the column in my spreadsheet looks something like this:

MJR
DML
MJR
RJG
RJG
MJR
DML

I want to know how I can create a formula that will automatically tell me how many times "MJR" is listed; how many times "DML" is listed, and so on.

### Border every other cell in range

I'm trying to write a macro that will apply a bottom border to every
other cell in a range. the macro only needs to work for horizontal
ranges, and not for vertical ones.

the closest i can get is the code below, but this only applys borders
to cells in odd columns. i need it to apply borders to every other
cell in a selection, regardless if it is even or odd.

Sub BotBorderOdd()
For Each cell In Selection
If Application.WorksheetFunction.Odd(cell.Column) = cell.Column
Then
oRange = oRange & "," & cell.Address
End If
Next cell
oRange = Mid(oRange, 2, Len(oRange) - 1)
Range(oRange).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

### Delete every other cell in row

The following code deletes every other cell in a selection starting with the first cell. How can I change it to delete every other cell starting with the second cell in a selection?

```Sub delete()

For Each cell In Selection
fClear 1 = Not fClear
If fClear Then cell.ClearContents
Next cell

End Sub```

### Replace every other space in a string with "|"

Hello all,
I need to programatically replace every other space in a cell with a pipe symbol, in order to seperate stock symbols and their market indicators from each other. For example, right now I have 3,500 rows with data that looks like this:
BBCA IJ BZG2 GR PBCRF US

which I need to turn into this
BBCA IJ | BZG2 GR | PBCRF US

The length of the tickers is unpredicatble, but the pattern is consistent.

Any ideas?
Thanks!

### Line chart for every 5th cell in a row

I have no idea how to do this, i cannot select individual cells manually because there is too much data and i want it to pickup every 5th cell in the row.

I want one range on the line chart to plot B17, G17, L17..... etc.

Then i want the next range to do D17, I17, N17.....etc

So going up 5 letters of the alphabet at a time. One starting at B one starting at D

Any ideas?

Cheers
Kev

### How to lookup and match multiple cells in a row

Hi I have a workbook with 2 work sheets, I need to lookup and match three cells in any row and if found on the other sheet return an invoice number from that row.

I'm not doing a very good job of explaining myself so I have attached an example spreadsheet,

I need to lookup the DATE, PART and QTY from sheet 1 against the table in sheet 2, if a match is found in then I need to return the invoice number for that row.

I really appreciate any assistance.

Regards,

Paul

### Sorting cell and associated cells in a row

Hi
I've created a document and userform which allows me to enter my receipts into the following columns: date; item; item desc; net vat; vat: item cost.

I wish to be able to enter the recipts in any particular order, but wish to be able to sort the document out later by date.

Which is no problem except it only sorts the date column, and not the corresponding associated data/columns

Is there any way to be able to associate or link the other cells in a row with the date cell and be able to sort by the date row and effectively the rest of the data.

### How do you use countif for values in every four cell in a row

How do you use COUNTIF to check certain cells in a row that have a value of
zero.
cell C4, cell G4, cell K4, cell O4, cell S4, ....etc

### Automatically Select cells in a row

Hello,

I need a help to automatically select the whole cells in a row. Basically what I'm currently doing is recording Macros and apply the '=myformat' function in the first cell then copy paste it to the whole row.

But now the problem is when a new data is added because then my macro can not look into it. Below is the VB code from the Macro;

and FYI - I have no knowledge in VB at all, but I'm trying to learn

PHP Code:
```    Range("O2").Select
ActiveCell.FormulaR1C1 = "=myformat(RC[-1])"
Range("O2").Select
Selection.Copy
Selection.End(xlToLeft).Select
Range("N2").Select
Selection.End(xlDown).Select
Range("O18606").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.End(xlUp).Select
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=myformat(RC[-1])"
Range("R2").Select
Selection.End(xlDown).Select
Range("Q18606").Select
Selection.End(xlUp).Select
Selection.Copy
Range("R2").Select
Selection.End(xlDown).Select
Range("Q18606").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.End(xlUp).Select

```
Cheers,

### Formula to calculate average of every other cell

Hello,

I have a row of data starting in cell E4 that could, theoretically, go to the far right end of the spreadsheet. I need to enter a formula in cell D4 that calculates the average of every other cell in this row, starting with E4, that is E4,G4,I4,K4... is this possible?