I attached the extract. I am having the issue in the Total Workbook. Specifically, the Tax and Debit Column. If a company is on both workbooks (Insider Processor, Outsider Processor). The Tax and Debit should be charged once in the Total Workbook. But i don’t know how to get this happening
View 6 RepliesI have a statement from an account (which happens to be the government) in which they list every invoice they are paying and each item on that invoice. But they don't have an invoice total. I'd like a way to add up the item totals for each invoice and put the total in column D. Each invoice could have 1 to 10 different items on it.
A(invoice#) B(Item) C(total) D(invoice total)
111 widget 1 $5
111 widget 2 $10
111 widget 3 $8 XXXXX
222 widget 1 $5
222 widget 5 $15 XXXXX
333 widget 2 $10 XXXXX
444 widget 5 $15 XXXXX
I had thought an IF formula would be the way to go.
I have code that selects chart source data by searching the header row for dataTyp (in this case the dataTyp will be set to "IMP"). The target header cells look like "IMP 100Hz", "IMP 400Hz", "CAP 100Hz", "CAP 400Hz" etc. recently I inserted an averaging column at the end of each group of data columns. The headings for the new columns look like: "IMP AVG" "CAP AVG" etc. I need to exclude the ...AVG column from the source data selection. Code snip:
View 2 Replies View RelatedI am trying to get 2 different formulas to run depending on the value of the 2 cells.
Example: if cell "A" is equal to cell "B" then one formula, if it is different than a second formula.
I hope I did this right, if not please let me know and I will ask the question in a different manner.
I have a workbook setup to give me the nutritional break down of all of the foods I consume. I can get the subtotal of each day. What I need is to also get the subtotal of each meal. I can't seem to get this meal subtotal without adding it to the daily total twice (once in the listing and once in the subtotal). I was thinking of trying to add an exception to the sumif formula in columns I, K & M but I don't know if that is possible. I have included a cut down version of my workbook to illistrate the problem. I will gladly supply a full vertion to anyone who would like a copy for thier own use.
View 5 Replies View RelatedI am trying to write a macro that looks at columns C-I in row 6, to check if the value is G, if it is then I want it to delete C5,6,7 & 8 and so on along to coumn I. So all that I am left with at the end of the macro are the values that weren't green without any blank gaps in the columns. I started using the following
If C6 = G Then
Range("C5:C8").Select
Selection.Delete Shift:=xlToLeft
End If
And using that same code for D6, E6 etc etc, but obviously, when it deletes the cells it shifts them left so the columns change values, so if C6 is equal to G then it deletes it but then what was D6 becomes C6.
If Cell value in "C36" or "D36" is Yes, then a picture will be displayed (either Picture 1 or Picture 2). If Cell value is No, then the pictures won't be displayed.
The code I use:
[Code] .....
By default, all pictures on the Excel sheet are invisible.
My problem is, there are 3 pictures I'd like to display at all time because they are part of the document's decoration.
Therefore, I'd like to add an exception for the Picture A, Picture B, Picture C so they are not affected by the command "Me.Pictures.Visible = False".
I am using the below code to force a range of cells to change to Upper Case, what i am after is an exception to the rule for example the words Pick Up i don't want to force to upper case, is there VBA i can add to the below to achieve this?
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''
'Forces text to UPPER
''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("w6:w299")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub
000108159900
0.97
000108160000
1.82
000108160000
0.38
000108160000
0.37
There is one value for 000108159900, which is 0.97 and three values for 000108160000. This is just a very small sample. I have about 12 million such rows. What I'm trying to do is create a separate column that will say 000108159900 has a total value of 0.97 and 000108160000 has a total value of 2.57. How can I do that?
I have a big Excel file that is supposed to be used by many users and therefore I need some cells locked. But problem is I use these cells in my vba codes which the users must be able to run by using the buttoms in the file. But now I get error in every macro since the cells the code are trying the reach is locked. Can I make an exception for vba codes in some way so that I can run my macros?
View 4 Replies View RelatedI have a column of numbers (each cell represents number of chicken eggs produced daily). Column data runs from K9:K415
I want a formula in column M starting at M9 whereby cell M9 sums up the total number of eggs for the week (i.e. sum of K9:K15), then I want to copy that formula down, such that M10 shows the sum total of eggs for the next week (which is sum of K16:K22) and so on. Thus summing up every 7 rows of data in column M.
I have been playing with the SUM and OFFSET functions but can't quite nail it.
Without having to type =SUM(D1,D3,D5,D7,D9, etc...), what is a quick way to sum, say, every odd or every even cell in a column or row?
View 9 Replies View RelatedCCTRCCTRCCTRCCTR112#112NB114#114NWSales group112112114114MONTHQTRYTDNielsen ID#NB##JAN109Q10920091/2/20092894.1717.161231.644704.9JAN109Q10920091/5/20093188.9544.22766.313039.35JAN110Q11020101/6/20092867.92945.571890.774520.44JAN110Q11020101/7/20091927.89210.58484.94960.61RESULTSTABLE 1TOTALS11211410961459742110595211857RESULTSTABLE 2TOTALS112#112NB114#114NW109608361199877441104796115623769481
Table 1 and Table 2 are examples of the results that I need - I have a formula that works for table 2, but when I need to consolidate the numbers as in Table 1 - it won't work - it only views the first 112 or 114 and doesn't sum them together.
the formula that I'm using for table 2 is:
=SUMIF(REV!$B$5:$B$574,"="&$A$50,INDEX(REV!$E$5:$DD$574,0,MATCH($C15,REV!$E$4:$DD$4,0)))
how do I adjust this formula to sum all 112's and 114's in row 3? it sums the columns good, but I need that extra component to make this project work.
why a macro that works without a problem in Excel 2010 now crashes Excel 2013. The error produced says 'Automation Error Exception Occurred'. If I place a stop in the code and hold the F8 key down, the code runs perfectly (Excel 2013) but if I run it, it will crash. The code is copying a worksheet into a new workbook, adding 2 more worksheets, saving then emailing the workbook before closing. It goes through the routine about 7 times before crashing (sending only 7 email out). It's something to do with the email because I can bypass that code and it runs that part without a problem.
Our office just upgraded all the computers to Office 2013 and I've got to get this macro running again. I had to email 100 reports one-by-one today and I didn't like it.
Currently i am using index and match in excel but however i am unable to get the result tt I want.. what i need is
Data to check with item master whether all 3 (article, color code and size) matches and if it doesn't match, which of the field is wrong. (eg: wrong color code, wrong size or article don't exist).
[Code] .......
Above is my item master
Data to check
[Code] ........
Example case: 1
Checking first row of data check
Article number: 2-50660147
Color code: 6
Size: M
Result: "Wrong size"
But however, it returns as wrong size as it only reads the first time the article number appear in the item master and does not recognize the second time the article number appears with the correct color and size.
I added an attachment. Column A is my "week number" and column E is my sales amount.
I'm trying to create a formula that sums the sales amount, only if it corresponds to a certain week number. I know sumif formulas, but can't get them to work well for this. For example, I want it to sum all the sale that happened in week one, then all the ones in week 2, then all the ones in week 3, and so on. There has to be an easier way than doing a sumif for each individual week and manually changing the week number in the sum if formula.
Could there be a character you can use in a formula to make the sumif week number "running?" So you could then just type in the formula once and pull it all the way down for the year, so the formula will work for the every week throughout the year?
I often look at utility data (start date, end date, use, and cost) and have a large list with up to several years worth of monthly data. I am trying to write a formula that will automatically sum the last 12 entries of "use" no matter the amount of lines; be it 12 or 112.
View 2 Replies View RelatedI'm working on a worksheet that has dates in one column (column A), and numbers in another column (column B). What I'm looking to do, is look through all of column A, find all entries between a certain date (ie Nov 1 07 - Nov 30 07), and sum all the values in column B that correspond those fields, as long as the value is greater than zero or not blank.
So, for example,
A B
Nov 1 1
Nov 6 -5
Nov 3 6
Dec 6 5
Jan 1 2
I would need the formula to return 7
I would like to sum G column (in my sample) that will meet a date criteria in C (like from 10/9/2009 to 11/9/2009) or similar. I tried it already with a formula =IF(c2=DATEVALUE("10/9/2009")=<("11/9/2009"),G2,0) could not make it work but its okay without the =<, or a single entry date (a sample in AB column), with this formula its being transferred to another column and being summed, what am trying to figure out is to make a formula or function that it can be incorporated in a sum at the bottom of G column if possible, I would be able to check how much cash collected in a week, days or month....
View 4 Replies View RelatedHow Do You Calculate A Column of names
Say I have starting on line 3 names of people in column D
What is formula to calculate all the names I type in this colomn D
Attempting to sum Book Values for Units.
Unit numbers in one column, Book values in adjacent column
Some Units have one occurence, others have multiple.
Some Book Values will sum up to 0 while most will have a positive value.
Attached shows small portion of data that presents range of issues I'm working with.
I need to sum all the rows, except the header row, of a column inputted by the user. The column will be located in a worksheet dubbed SourceSheet.
Here's a few pieces of code related to what I am trying to do. The header row is always row 1. So the rows I'm summing are always 2 - myrows.
I have the following sheet with reg hours and ot hours. I need to insert a column that sums these two items. I used the macro recorder and it only works when the same amount of ID's or less. If there are more ID's it does not sum the remaining ID's. What it currently looks like:.........
View 3 Replies View RelatedI am working on a task analysis involving functions performed by different operators, each function marked by an "X" and totaled at the bottom of each operator column. I have seven sheets, named "Main Engine, Propulsion System, APU and ELEC.", etc. Columns B-F on each sheet are headed "C/M, Engr, Nav, LM, DE" respectively.
I need to add/total the bottom figure in each column (total of number of X's for each column) on a separate, final worksheet. Each of the worksheets contains a different number of functions, therefore the total of X's in each column is listed in a different cell# on each worksheet.
So this is my dilemma as the thread title states: I need to be able to sum/total the amount form different cell numbers (same column) across different worksheets into a cell in a separate, final worksheet.
I have figured out how to do this if the numbers are in the same cell across worksheets, but not varying cells by column.
Imagine column A sorted by invoice numbers. Column B has payments applied but due to movement between unapplied cash, some numbers appear as negatives and positives for the same invoice number.
I want to have a formula which says that for each invoice number, look in column b and eliminate opposite numbers and post only unique values. Keep in mind that there may be multiple lines with the same invoice number.
For example, starting with line 5, for invoice 123, payments 400 and -400 in line 5 and 17 and payments 315 and -315 in lines 7 and 9 will zero out but any other unique payment will be picked up and displayed in column C. Lines 5 and/or 17 will be blank in column C.
I have been trying multiple SUMIFS and SUMPRODUCT formulas to try and revamp my budget spreadsheet. My first criteria is the month being a certain number (contained in a column), if that condition is met it needs to look for the account number I am budgeting for (row heading) and if it matches both criteria I need it to sum the Column that has the row heading of the account number. I have succeeded in doing a SUMIFS and manually finding the column that has the heading of the account number I am looking for, but I would like this to be an automated process so I can copy the formula. Below is the data I'm using (a very, very small portion of it), and the results are how my budget sheet is going to look. These will be on separate tabs in the workbook.
EX: For July electric revenue (44211) I need my formula to: (if the Month column=7 & if the Account Code row heading=44211, sum the column that has the heading of 44211). So far I cannot get this to work.
DATA
Account Code
44211
44215
[Code]....
how to code VB to where when data is sorted a formula can be placed at the bottom a column to sum or average the above data?
I have tried relational positioning e.g
each time a row is created, integer n5 = n5 + 1
Application.CutCopyMode = False
ActiveCell.Offset(rowoffset:=1, columnOffset:=12).Select
ActiveCell.FormulaR1C1 = "=sum(r[" - " & n5]c:r[-1]c)"
ActiveCell.Offset(rowoffset:=0, columnOffset:=7).Select
ActiveCell.FormulaR1C1 = "=average(r[" - " & n5]c:r[-1]c)"
Application.CutCopyMode = False
n2 = ActiveCell.Offset(rowoffset:=1, columnOffset:=-19).Address
n5 = 0
Where n5 is augmented each time a row is created then set to 0 to count the next list
Problem,
A B C D E F (B+C-D-E)
Date Opening Stock Purchase Sales Consumption Closing Stock
17/7/2012 10000 5000 1000 2000 12000
17/7/2012 12000 2000 2000 1000 11000
18/7/2012 11000 0 1000 5000 5000
18/7/2012 5000 10000 3000 0 12000
Note : I want the above result as shown below datewise but no duplication of date but summing up of column C,D and E which is in above.Pls note that this is accounting question and also plz note that Opening stock(column B) from next date onwards must be equal to previous date closing stock(Column F).
Results :
A B C D E F (B+C-D-E)
Date Opening Stock Purchase Sales Consumption Closing Stock
17/7/2012 10000 7000 3000 3000 11000
18/7/2012 11000 10000 4000 5000 12000
(B=F)
if I could get the formula in excel ,since this is the sample data as I do have a numerous & huge data like shown above datewise where I need to add C ,D and E but Opening stock(column B) from next date onwards must be equal to previous date closing stock(Column F).
I have a table that looks like the following, only it's actually much larger:
_Red Blue Green Blue
A 2 4 2 3
B 5 2 1 1
C 3 1 2 5
D 2 3 4 2
As an example, I'm trying to sum all cells that match Blue and C. The answer should be 6, but I always end up with either zero or #VALUE.
Excel 2007. I have an Excel file that contains a data dump from an external database file with numerous analytical sheets that perform calculations. Some of these calculations utilize the SUMIFs function that was introduced in Excel 2007. This function does an outstanding job of summing a column on the data sheet based on multiple criteria.
However, someone high up in management in my organization would like to "drill down" into the data behind the
SUMIFs formulas to get a quick snapshot of the lines in the database that roll into the
SUMIFs formula. =SUMIFS(DataBase!E:E,Data!A:A,C7,DataBase!B:B,D7,DataBase!C:C,E7,DataBase!D:D,F7)
If I double click on a cell with the formula above, Excel takes me to the Database tab and selects Column E which is close, but not exactly what I need. What I really need is for Excel to only show the rows on the database sheet that make up the total in the SUMIFs formula and not the entire data dump from the database.
At present, we have to manually apply the autofilter on multiple columns to show the rows in column E that make up the total in the SUMIFs formula which is a tedious and time consuming task. Is there a way to force Excel to do this? Suggested custom database application or pivot tables, but we do not want to reinvent the wheel.