I have three columns.
For instance, Column A are names. Column B are totals points and Column C is year.
I want to a cell too look up all names of "John" for the year 2007, then with those two conditions add up all total points for John in 2007.
I have a column of times: e.g. 10:03:00 and I would like to add them all up.
=A1+A2 works fine.
=sum(A1:A10) does not.
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E"
Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
What I would like to do is have a master workbook that I can import different .csv files into as a new worksheet. Then calculations will be run on the values that are imported. My goal is to select a column and have corresponding list update the values. After that formulas will run on the calculations. I have got the import csv file down using VBA. The problem I was running into was with the Data Validation. Since I was overwriting my existing sheet I would get a #REF error because the link was broken. So I have worked my way around this for the list using the OFFSETSHEET Function:
VB:
Function SHEETOFFSET(offset, Ref)
[COLOR=#0000ff] ' Returns cell contents at Ref, in sheet offset[/COLOR]
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function
This returns a value to a row in my mater sheet and I reference this for the data validation list.
However, I get the same problem when working with the HLookup function, the #REF error occurs. So far the only way I have figured out to work around this is to create another sheet that dynamically updates its values using the same OFFSETSHEET function, and my master sheet then references it.
I want to add across columns, which is not so difficult but I have to "ignore" the blank cells because of the way my formula works.
I'm not sure of the correct mathematical term for the addition I have to do but here is my example:
in this row are the values
1 2 3 4 5
In the row below I want to add the numbers so the result is
1 3 6 10 15
basically, a cell is always adding itself to the result of the sum from the previous cells. This formula I can handle, (=A2+B1 then autofill the results by dragging the formula across the empty cells) but when there are blank cells between values I get the error message! value. How can I write a formula which will ignore the empty cells?
i have a problem adding cells every 5th cell for example i have
=SUMPRODUCT((C10:C67)*(MOD(ROW(C10:C67),5)=0))
this adds every 5th row in this range and it does it right as long as there is no text in the cells in between
it works fine when it looks like this
1
1
1
1
1
1
1
but it does not work when it looks like this
1
1
text
1
1
1
when this happens it appears as #VALUE, meaning an error but i don't understand why if i'm just telling it to look at every 5th row and those specific rows have no text in them
i have a spreadsheet with text in between the cells that need to be added so i need a formula that only adds up the cells with numbers on it, to be specific the numbers are either ones or zeroes.....
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
I m adding cell with alphabets. I attach a pic of what i want it to look like. Is there a command to do it for you instead of me outting one by one myself? Ex. 'A' must first followed by 'B' and so on, futhermore, if there a CApital 'A' and a lower case 'a' the capital 'A' goes first.
View 14 Replies View Relatedevery month i download a sales list of which there are about 200 transactions.
each day there is a different amount of sales ie
feb 1st 9.99
feb 1st 14.99
feb 1st 7.99 (3 sales)
feb 2nd 8.99
feb 2nd 16.99
feb 2nd 14.99
feb 2nd 8.99 (4 sales)
"" "" "" ""
etc throughout the month
at the moment i am using the SUM to maunually add the sales per day
feb 1st 32.97
feb 2nd 49.96
as i need a daily record of income, however this is very time consuming
is there any way i can request the worksheet to add say column b for the 1st feb and show total
column b for 2nd feb and show total, thus saving myself hours of work ??
way to add up the total for each day
Here is what I have In cell H4 I put the following code =B5:G5
But when I add number in thous cells nothing happens. I want cell H4 to keep a running total of cells B5 through G5 so as I add numbers to the cells I would like the total in H4 to change.
All I want to do is the following;
I have a workbook with 18 columns and 9000rows for each column,
All I want to be able to do is ad a"," (Comma) in each cell at the end of what ever is in each cell.
Tried looking in the help of excel, but if you can't write in properly what you want, it doesn't give you the answer of how to do this in the help list
While cells(r,40) or cells(r,41) are empty error 13 occures
Dim r
r = ActiveCell.Row
Cells(r, 43) = Me.TextBox3.Value
Cells(r, 41) = Cells(r, 41) + Cells(r, 43)
Cells(r, 42) = Cells(r, 40) - Cells(r, 41)
Why doesn't excel "see" empty cells as null?
I have a column of cells formatted with different colors to represent a range of values. I want to know if that a formula or VBA I can use to I sum up these range of cells with the same color.
View 10 Replies View RelatedI am trying to add a ROW of times. Each cell of times is using the 00":"00 format so I don't have to type a colon between the hours:minutes.
I'm not sure of what the formula should be for the Total Hours box.
For example, if I were to type 13:45 in the start time & 14:00 in the end time, I am getting a result of 0:55 which is incorrect
Another example if I leave the first 2 start/end boxes empty(blank) and then type 15:30 Start & 17:00 in the second set of Start/end boxes, I get a Total Hours of 1:70 which is also incorrect.
The formula I have in the Total Hours box is =SUM(B12-A12)+(D12-C12) which is know is incorrect.
What is the correct formula which will give me a Total Hours which adds a row of times correctly?
StartEndStartEndTotal Hours
I have a worksheet that has 5 rows and 5 columns. The five rows are Highschool subjects. Math, English, French, Phys. Ed, History. The Five columns are Monday, Tuesday, Wednesday, Thursday, Friday. I want to device a worksheet that say for Monday it will have YES for math, no for english, yes for French, yes for phys. ed, no for history. Tuesday would be say no for math, yes for english, no for french, etc, etc. I then want to use an if statement to convert the yes & no entries to a numeric value. I.E I have this right now. =If(B6="YES",1,0)
This simply puts a 1 for a yes and 0 for a no into cell M6. What I want tho is each subject to add the amount of yes's for the week and put it into cell M6. So if math is taken 3 times a week it would put a 3 into cell m6. if History is taken 4 times a week it would put 4 into cell m7, etc, etc.
1) I am trying to find a way to add together the total number of hours worked from shift start time to the end of the shift start time throughout the week.
2) Add together the total number of hours travelled. again from the start and end times throughout the week...(presumably, if I solve the first problem, it in itself will provide the second solution.!! (Grin)
Where shift start and end times are concerned, the cells are formatted as time, but I want the sheet to produce the results of the total number of hours worked, so that I may cost the hours and make payment. Ie: 62 hours worked = £ at such and such a rate, and 25hrs travel paid at £ different rate.
in a cell range a1:a5 I have the following data .5v .25s 1v .75s .5v. I want to add all numbers with v and s separately so that in cell a6 I can have totals of v i.e. 2v and in cell a7 1s
View 2 Replies View RelatedI recieve cost sheets from a company and I want to be able to change there number into a formula and mark it up all in one. How would I do this without going into each cell individually and make the change? Example say the number is $100 in the cell I want to change that to =$100/.6 to get a 40% margin. Then I can give this to salesmen with just the retail number but I can see the formula and cost.
View 9 Replies View RelatedI have a spreadsheet with 4 columns.
A, B, C, D
I want to multiply A*B and then add that to C*D
=SUM(A*B)+(C*D)=TOTAL
However, when I dont have a value/number in A or C I get a #VALUE in the total cell. It will only calculate properly when I have numbers in all 4 cells.
Is there a way to set it up so it will calculate if cells A or C are empty? I dont want to have a zero in these cells - just leave empty if no value.
Also - when the total cell is empty I want it blank.
I have a column of barcodes. I need to get a formula or script to put |Pipes| as the before the 1st and after the last character in the cell.
E.g. barcode 123456789 should be converted to |123456789|
I then want all the individual barcodes to appear in 1 cell next to each other
E.g. in one cell |123456789|987654321|244346473|686541234|479854635|132498569| and so on.
I have the new USPS rates in an excel sheet. I need to add my handling fee to all of the new rates. Is there a way to quickly add this same .70 to all cells?
View 2 Replies View RelatedI have about 5000 rows of data and I need to add the same letters (.jpg) the the end of all the data in a column.
View 4 Replies View RelatedI need to calculate ratings 1-5 of questions on client surveys received.
I have recurring sets of data, A1:E1, a number 1-5 is to be entered into one of the cells within this range. This same pattern is repeated 25 times....G1:L1, etc. At the end I need to total all the 1s, 2s, 3s, etc. Then I need to calculate an overall pct for each question based on the totals for that question, divided by the maximum score (5) multiplied by the total surveys received. The problem I am having is that my formula below is counting empty cells, and not giving me a proper pct.
=SUMIF(A1:E1,">0")/(5*$B$1)
I want to set up a cell so that when the cursor is on the cell a drop down option appears allowing the selection of a yes or no to be shown in the cell. What's this called and how do I set it up?
View 3 Replies View Relatedcan some one help me how to add the unique cell values using the formula bar?
like if on column C i would like to add all the row values which have the name "George" how can this be possible using the formula bar?
When adding a comment I check for an existing comment, if exist then delete and add new comment and some text in that cell. This works as long as the cell has an existing comment; other wise it advances to the "Else" where I thought I would be entering a comment and text in an empty cell. Why does my macro treat an empty cell as if it has a comment?
View 4 Replies View RelatedLet me explain this as best I can:
I have an excel file with multiple tabs on it. Each tab has the exact same format with different numbers. On the last page I want to add cells from each tab and have the sum go to a cell on the last tab.
I need to create a way to generate specific URLs based on a fixed URL and adding a variable number in a cell to create that URL.
example of the finished product
[URL]
So I have:
Cell B is http://www.boxpool.com/=
Cell D is 99
What formula should i use to generate [URL] so that different numbers can be used to generate unique URLs.
I'd like to sum up values based on certain cell attributes. Firstly, I don't know if it's possible to define cell attributes and how to do it. Let me put some example:
I have one column where I've put some values and I'd like to sum up the values only for certain cells in this column. In an "OOP approach" I'd use some grouping method based on attributes, so each cell would have an attribute called ID, for example, and I could select only the cell with some desired ID.
I'm looking to easily drag the sum of certain cells in a different column BUT keeping a specific range, it's hard to explain so i'll show an example...
A1
A2
A3
A4
A5
A6
A7
A8
B1=SUM(A1:A4)
B2=SUM(A4:A7)
B3=SUM(A8:A11)
And so on...
Is there any way I can do this by dragging down the cell formula from B1 and it remembering the range of 4, so I don't have to manually select each range...?