Sum Alternate Cells
I need to sum the contents of every fourth cell in row A .... ie sum(A1 + E1 + I1 ... BY1)
Can I do this via the formula bar, or do I need to use a VBA routine?
View Complete Thread with Replies
Related Forum Messages:
Sum Alternate Rows
I do an analysis that contains 100 or more rows with 1 of 2 row labels in column A; "existing" or "retrofit." There may be numerous successive rows labeled "existing" in a given place where there is no retrofit. Rows labeled "existing" contain existing equipment details such as area, equip description, operating cost. Rows labeled "retrofit" contain recommended efficient replacements with details such as area operating cost and savings. I have to display total cost and savings. Right now I use the awful method, F1+F3+F4+F5... for existing and F2+F6... for retrofit. This is a terrible method, time consuming, prone to error, etc. Is there a way to total rows with specific labels when the row labels are not consistently alternating? Would like to attach an example spreadsheet but... the permissions say I may not, for some strange reason.
Alternate Formula For Sum Indirect
I have tried to apply '= SUM(INDIRECT("A2:A10"))' formula to do the SUM at cell A11. But, if I add two more Rows, then my formula moves down to cell A13 but numbers in Cell A11 and A12 does not get added to the total. How can I avoid that? I have reserached this site extensively and could not find an archived solution.
Sum Alternate Columns Base On Previous Column Entry
I am trying to resolve a calculation issue where I want to sum accross columns depending on an entry in the column immediately preceeding. The layout is an Attendance sheet, The columns are for the days of the Month ( 1 - 31 ) and the rows are the Months. There are 2 columns associated with Each day. The first column is for the type of Time Off ( Vacation, Sick, Personal, etc ) the column next to it records the number of Hours some one took off. The work book has a Sheet for Each Employee and a running total needs to be maintained for the amount of "off time" each employee takes by the various time off categories. I have tried setting up range names but this won't work as there will be multiple sheets. I believe the problem is the mixture of Text and Numeric data but could not resolve.
Countif In Alternate Cells
I have a table of data that is laid out in multiples of two columns. I have attached a simplified example. Essentially all I need to do is count the contents of every second cell when it equals a specific value. If you look at my attached example it will be clearer. how I can do this using formula? (I don't want to use VBA in this instance).
Formula To Add Alternate Cells
I can't remember any formula to do the task. I have a time series with quarterly sales. The dates are in format given below.
01/01/1990 - first quarter of 1990
02/01/1990 - second quarter of 1990
03/01/1990 - Third quarter of 1990
04/01/1990 - fourth quarter of 1990
I need to add the sales of all first quarters , all second quarters..and so on.
Is there any formula that an elegant way or I have to do it manually?
Macro To Populate Alternate Cells In A Row With A Value...
I have a spreadsheet for work rosters. Each person on the roster has a row with info on what they're doing for a given week. Split into the am and pm session of each day.
All I want to do is be able to fill the row with the same value if they're doing the same thing for the week - eg if they're on leave, I'd like to hit CTRL-L and have the row populated with 'LEAVE' in each alternate cell, rather than typing it manually. Note that it is each alternate cell, not each cell as the alternate cell has different info.
So a row would look like this:
Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |Leave | blank |
For the am and pm session of the five working days. And just to complicate matters, each 'cell' on my spreadsheet is actually 4 merged cells, and the alternate 'cell' that I want left alone is two merged cells! This is the macro generated when I do a simple record - it does what I want, but obviously jumps to the original row that I recorded it in whenever I run it - I need it to fill the row that I start it from. I recorded starting in cell I133:L134
Change Colorindex Within A Range Of Multiple Cells [ Alternate Method]
I have tried to go around the long way to achieve this but came up with pages of pointless code .... I know there is a better way I just dont know enough about VB to do it myself ... And I know this is EASY for many :-)
Cell ranges h11 to as11 are a totals row.
If the total is 0, colorindex is set to vbpatternnone, if >= 1, then colorindex is set to vbpatterngray. Easy right ? I just dont kn ow how to do FROM/IF/DO range loops...
The code in worksheet_SelectionChange will contain the following:
1: From range h11 to as11, variable1 = application.interior.colorindex of the cell.
2: Check if the cell is >=1 or <=0 ....
3: If >=1 then set application.interior.colorindex = vbpatterngray. Go to #5.
' (This inserts a pattern over the original color of the cell)
4: If <=0 then set application.interior.colorindex = vbpatternNONE
ALSO set application.interior.colorindex = variable1
' (This clears the cell pattern and returns it to original color)
5. Repeat steps to clear cell pattern and restore color / or insert pattern for all cells from range H11:AS11
6. End sub
Find & Copy Cells & Paste To Alternate Columns
I have a spreadsheet that I would like to loop through column "C" and if criteria is met copy and paste A:C on sheet1 to sheet2 over multiple columns alternately. What I mean by alternately is that I would past the first row in column A6 then the second in E6 then the third in A7 and so on until all items are copied. I purposely left a blank column between both columns of information. I've tried sorting/and advance filtering and couldn't get it to work.
loop through column "C" If I have the Letter "A" copy data to column "A" and "E" alternately back an forth until I no longer meet the criteria. I start putting data on the 6th row due to header information in rows 1-5.
... If column "C" is the letter "B" copy to column I,M,Q,U
Lastly I could always have less rows of information than I do columns. SO the last column could be empty. I always sort my data by column "C" so data will be sequential.
Alternate Is Statement
i have attached a copy of an excel file and if you look at the end i am trying to write an IF statement that reads if J = "2-0" then column M = 12-(K) or 12--4 so answer is 16 and then column N = -column M or -16 and then if J = "2-1" then M = 6-(K) or 6--0 = 6 and then N = -M .....
so if J was "2-1" and (K) -4 then the answer given for column M would be 6--4=10 and column N = -10
Alternate Way Of Displaying Data
I have been using a lot of sumproducting lately with multiple conditions to extract data. Lately I have noticed that though it is a good way to extract data there is a lot of calculation time involved in it. The Excel workbooks that i make are in a database like format where there is 1 sheet usually a data dump which has data from one column to the 200th column and rows being filled with data points till the 10000th row. Data headers in the columns are usually like Date(ColumnA), Tenure(ColumnB), Person, Type, etc and then from Column Z onwards there are columns which contain Data in the form of numbers like Number of cases, Number of this and number of that.
Now usually when creating a dashboard of this data for performance management I use the sumpoduct formula to retrieve data. It normally has conditions in it like for some given date ranges, Tenure ranges, People ranges extract x data for me. For Example something like this
Reference To Alternate Tables
I want to reference to a range of tables depending on the value of a separate cell. Problem is that I don't know how to insert this into the range part of the lookup formula.
=vlookup(a12,NAMED RANGE AS PER CELL c9,2,false)
Cell a12 is the lookup reference which is fine.
Named range is set-up and working fine.
Cell c9 is the description of the named range - season_indices_Asda_Cream
I want that to be changeable by he user so they can change the name in cell c9 from a drop down list so that the lookup formula redirects to the alternate named range...
Limitation Of 7 Nested IF Statements - Alternate
I have a workbook with two worksheets, Sheet1 and Sheet2. Sheet2 contains a table of values that need to be input into a cell on Sheet1, pending the results of comparing two other cells on Sheet1. I have 8 possible variations resulting from that comparison and I cannot make this work as the IF statement limits you to 7 deep.
A1 (text string value) = LOWER
B1 (text string value) = L1
C1 (currency with no decimals) = Sheet2!Somecell (decision of which cell to use depends on combination of A1 and B1)
A1 can be either the string "LOWER" or "MIDDLE". B1 can be the strings "L1", "L2", "L3", or "L4". The strings in B1 are not cell references, but simple text. This leads to four variations for a row that has "LOWER" in it's A column, and the same for "MIDDLE" - totaling 8 possible combinations.
Depending on the combination, I need to input a number from Sheet2 and that number is different for each unique combination of the eight possibilities. There is no mathematical calculation taking place on Sheet2 - just an "if x and y then z" decision on Sheet1. I will use the value of Sheet1!C1 in other math functions on Sheet1.
Copy & Paste In Alternate Columns
I'm trying to copy and paste range in alternate columns from one worksheet to another. I can record this macro, but I believe it'll be really long because I have 21 alternate columns to copy and paste. What I'm trying to do:
-copy range B9:B41 in workbook 'Channel OU template' then paste values only in range BI9:BI41 in workbook 'final'
-copy range D9:D41 to range BK9:BK41
-F9:F41 to BM9:BM41
..and so on until the last column AP9:AP41 to CW9:CW41
Basically it's just simple copying and pasting from alternate columns. This is the really basic code that I have just for one column:
Windows("Channel OU template").Activate
Alternate Button Macro & Caption Between 2
I have a worksheet with 2 buttons labelled "Hide" and "Show". As the names imply, they allow the user to hide or show parts of the worksheet. I would like to combine them into one button and have the button label and the associated macro change with each press of the button. Here is what I have so far;
Selection.EntireRow.Hidden = False
Selection.Characters.Text = "HIDE LEADS"
Range("A1").Select 'is there a better way to remove the focus from the button than selecting a cell off the button?
Selection.EntireRow.Hidden = True
Selection.Characters.Text = "SHOW LEADS"
These macros change the label fine after hiding or unhding the rows but I can't find the proper terms to use to change the macro associated with the button (if there is one?)
Search Column Then Update Alternate Sheet With Offset
Here's another question for you excel junkies to solve, while I try to understand what y'all are doing. I have two worksheets named "MASTER" and "BEDS". What I need to do, in sheet "MASTER" is go down column ("K:K"). "K:K" has unique data in it. then for each row...
"X" .value = offset minus one
"Y".value = no offset
now I need to go to sheet "BEDS" and find "Y" in column "A:A" then
from "Y" offset + 3.value = "TRUE"
"Y" offset +4.value = "X"
there should only be one instance of "Y" in column "A:A"..............
Finding Min Cell Values Excluding Zero In Alternate Columns
I have an array that is 1 row high by 16 columns wide.
Each cell may contain a positive value, or a zero.
I need a formula to find the "Minimum value that is greater than zero" in
columns 1,3,5,7,9,11,13 and 15.
=MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
need the minimum value that is greater than zero.
If I use nested IF functions to exclude zeroes I run foul of the max of 7
SumIf Function To Sum Cells When Other Cells Begin With Certain Characters
I want to use the SumIf function to sum cells when other cells begin with certain characters.
I've toyed with a few ideas of how this could work, but i don't know how to specify that the cells need to begin with certain characters. The cells that would be the criteria and the ones that would be summed come out of an Oracle database (and i have no control over the way they're pulled out - yet) so the beginning characters are connected to extremely unique information, so i dont want that to be included in the if part, for obvious reasons.
Conditional Format: Alternate Row Shading Based On First To Letters In The A Column
I have a price list from a friend. The price list has to have every other row shaded. This part is no problem with the conditional format command.
The real trick, at least to me, is the background shade of the row is based on the first two letters in the left most column.
CP rows would alternate with one color while FL rows would alternate with a different color and FI rows would alternate with another different color. All other rows would have no shading.
MacOffice Excel 08 is used so no VB code can be used.
Calculating Sum Of Cells Only Where Adjacent Cells Are Blank
I am constantly editing this (we currently have over 100 accounts) and therefore the totals are changing.I have a formula for Total but I need formulas for the other two, based on when the cells in columns F and J are blank or have dates in them: For active, the total is the sum of all numbers in column M but only when there is a date in column F and a BLANK in column J. For yet to enter, the total is the sum of all the numbers in column M but only when both column F and column J are blank. At the moment, my accounts run from row 6 to row 142, with the first line of totals in row 145, however this is constantly expanding.
Double "For ...Next" Loop, Alternate Column Summation
i have spent hours on with just no idea how to do it.
Write a function named MyOddSum(R) that returns the sum of values in the odd columns in the range R. Use a double ‘For … Next’ loop structure to do the summation. (Hint: Use R.Rows.Count and R.Columns.Count to set the index counters for the double ‘For … Next’ loops. Also, use ‘Step’ keyword to specify odd columns.)
Sum Up The Value In Two Cells
i am trying to sum up the value in two cells --- however, one has a number value and the other has a "-" value .... i tried to use a simple = function to sum up the value of both cells but it returned a "value" error.
SUM Cells With N/A
I am trying to sum these cells with some of them having N/A. The cells that I am trying to add are:
Sum VLOOKUPs (not Cells)
I have a list of cells and for each of them I preform VLOOKUP in a certain table.
Now, I want to sum the values all the VLOOKUPs without viewing the results of each VLOOKUP. In other words, I don't want a cell for each VLOOKUP result. I am only interested in their sum.
Is there a way to do this?
Sum Of First Two Non-zero Cells In A Range
I have a row containing numbers that can sometimes be 0. something like
0 3 0 5 7 8 6 0 ...
I need to get the sum of the first two numbers greater than zero. ( 3 + 5 i.e. 8)
I can get the first non-zero value with
but i have no idea how to get the second one. or how to sum them, since they are array formulas.
Sum Cells Only If 1 Or More Contain Numbers
I have a Sum formula adding three cells placed every other column (e.g. a1,c1,e1) these cells also contain formulae which links them to cells on another sheet.
My problem is where I try to sum these three cells the formula returns zero when all three are blank. I do understand I can use the If function or the options function to hide zeros but there maybe occasions when the sum might actually result in a zero.
The cell needs to return blank when the other cells are blank as it changes yet another cell which is instructed to change colour according to its result, and 0 when no data is entered would give the wrong impression to other users.
Match Cells And Sum
I have a list of account numbers is row A. If the numbers match I want to go to column D and sum the total. I have searched the forum and can't find anything formulas specific to add a column when the criteria change (in column A). Is this possible in VB.
Sum Visible Cells Only
I have a need to sum only the visible cells in a row. Certain columns are collapsed depending on the month and there is a YTD colum that I only want to pick up the expanded or visible cells. I found a custom function listed below at Microsoft's website but it only allows for a single range to be entered and I need to pick 12 individual cells, for example A1, C1, E1, etc. There is other information in the cells between and that is why the range won't work. Is there a better way to do this or can this function be modified to allow me to do this?
Function Sum_Visible_Cells(Cells_To_Sum As Object)
For Each cell In Cells_To_Sum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
total = total + cell.Value
Sum_Visible_Cells = total
Sum Of Visible Cells Only
I have a large spreadsheet that I am using multiple drop downs in to sort for
different scenarios. I do not want to use the general data subtotal command
but each time I sort I want to get a total for the visible cells -
Sum Up Cells Containing Certain Words
im currently wonderin if the SUMIF function can have a WHERE syntax?
because i want to add up cells which contain these:
1 VC-ON US 2
2 MC-ON US 5
3 MC-OFF US (STD) 8
4 VC-OFF US (PREM) 9
in another separate cell, i want to retrieve the sum of numbers with the words ON US in column A. so the final result will be 7.
Sum The Cells In A Column
I need to sum cells in a column if cells in the same rows of another column have values entered.
I was able to accomplish this by,....... well I won't bore you with the details -but it wasn't a single formula
Is there a formula for this? Database?
Sum The First Five Cells In Column
In cell A1 the user shall enter a number. Let us say 10. Underneath I have a table with the size of B1:C20. In column B there are numbers (1,2,3,4,..). These numbers are fixed. If the user enters eg 5 I want to sum the first five cells in column C. If the user enetrs 15 I want to sum the first 15 cells.
Sum Cells In A Column
I want to sum cells in a column (A) for which cells in another column (B) are lower than a certain value specified in a given cell.
Exemple: if my columns A and B are:
1) 100 3
2) 210 5
3) 350 1
4) 150 2
5) 300 4
if I want to add all values in A for which cells in B are lower than or equal to 3, it's easy:
sumif(B1:B5, "<=3", A1:A5) will give the correct result 600 (that is, 100+350+150)
However I will need have a non constant criteria: instead of requiring the values in B to be lower than or equal to the constant "3", I would to specify that I want the values lower than or equal to THE VALUE IN CELL B1.
In fact, I would like something like this:
sumif(B1:B5, "<=B1", A1:A5) but of course if does not work because the criteria can only involve constant values and not other cells.
The reason is that I want to then calculate this for each line:
for line 1, find the sum of all values in A for which cells in B are lower than B1
for line 2, find the sum of all values in A for which cells in B are lower than B2
for line 3, find the sum of all values in A for which cells in B are lower than B3, etc.
The expected results, could then we displayed in a column C, which would yield:
A B C
1) 100 3 600
2) 210 5 1110.......................
Sum Of Cells From Vlookup
I am making a personal expenses sheet to calculate my monthly expenses. i have each expense on my CC marked according to a validation list I have (for example Gas, Grocery, Shopping, etc)
How do i combine the Sum and Vlookup formulas to return the total amount spent on Gas for instance. This will be used for each of my type of expenses.
if its not possible, I can probably just write a macro.
Sum Only Visible Cells
I have a table of data with a filter on the top and a sum total at the bottom. Whenever i filter the list the total at the bottom doesnt change. how do i change it so the sum only calculates the visible cells?
Sum And Countif From Two Cells
I have a formula =IF(AND(F14=3,G14<2)=TRUE,1,"") which is in cell J14. I also want to 'add' to this formula an 'or statement' (I am not sure of the terminology, so taking a shot in the dark). So it would continue to complete the calculation as normal, but if there is a '1' in K14 it would also output in J14 as a 1
Sum Offset Cells
I am trying to write a loop that will look at every instance of a specific item in a column eg. "2x6 spruce" then sum the cells in the next column (which gives the lineal feet of "2x6 spruce" required) .