How can I summate cells in a column *except* for those rows which contain a specific value in another column?
=SUM(A1:A10 where B<row num> !="SOLD"). I'm trying to add up the weights of items which are not yet sold.
I have a table showing the quantity of products that were sold to stores within a particular week. The following data are shown in each column: Distribution centre (DC) ID, Item ID, Item description, Store ID, Day of delivery, Invoice Number, Quantity delivered - see example attached.
I want to sum up the quantities of identical items that were delivered to the same store from the same DC. In other words, the following conditions have to be met: (a) Distribution centre ID has to be identical, (b) Item ID has to be identical, (c) Store ID has to be identical. To give a specific example as per the attached file, instead of having 2 rows for 2 deliveries of 5 + 3 boxes of chocolate to store# 1, I want to have just one row showing that store# 1 had 8 boxes of chocolate delivered, a second row for that store would show that store #1 had 10 boxes of tea delivered to it. I would like the summed up quantity data point along with the Distribution centre ID, Item ID, Description, Store ID, Day of delivery, Invoice Number to be in a new tab, i.e. I want to reduce the table size and do not want the original rows to be in the new table.
Since my table goes all the way down to the last row 65536, I would need the formula/macro to be able to recognise identical DCs/items/stores and then sum up the quantities (there are about 1500 different item IDs). Can you think of a solution??
I have attached a spreadsheet that I need to automate. Cell C3 is a drop down box of the raw data found on the raw data sheet. I would like B6-8, c6-8 and d6-8 to automatically updated and change depending on what users select from the drop box in C3. (so basically im trying to re create the yellow table with formulae/ vb.)
View 4 Replies View RelatedI am creating a billing sheet that has a column where charges are coded (to determine what they are for) and I want a "Grand Total" for everything followed by a breakdown "Total" for things with the same code. Is there a way to set SUM code to grab all numbers next to codes?? (EX. Column A is Billing Code, column B is Cost in $$; I want a total at the bottom that takes the different amounts in column B based on the various information/codes from column A).
View 2 Replies View RelatedI have written an SQL Stored proceedure which drags this information into excel.
All is well here, however because the information changes regularly adding and subtracting in row length it means I am having issues writing code for sums as a standard macro is absolute and not realative.
What I basically have is this ....
I need to be able to search through category(column H) and match with machine size category. Then for each category split up total time spent on each function. Granted this will be 6 formulas. I've searched through the forums and came up with what I have....
View 10 Replies View RelatedI try to add some numbers, it will display ### symbol rather than that Value.If i give Summation for 10+10+10+10+10 the result displays ### than 50. How to resolve this?
View 15 Replies View Relatedhow to make a summation formula in excel (not simply adding 2 numbers together)? I have one cell that I would enter a number into, and another cell which would specify the limit. So if the first cell A1 = 1, and cell A2 = 50, then the 3rd cell would automatically add up every number "n" plus 1 until n=50. ie: (1+1)+(2+1)+(3+1)+...+(50+1)=result. The limit would have to be flexible, so if I change the 3rd cell to 70 then it would calculate to 70.
View 9 Replies View RelatedAttached is my sample data...Indicative Data_Revenue 2.xlsx
My aim is to populate columns C to G (Q1,Q2,Q3,Q4) automatically via vba against a particular account name(in expected output tab).The account names will have a Key word (listed in the "List Of Account" tab)The Q1 Sum should be a sum of Q1 revenue against all such accounts containing the Key word. Same applies for Q2,Q3 and Q4.Each account has 3 types. viz, BAU, Top Commits and Strong Prospect.
The BAU quarter sum should be pulled from "Revenue 1 - Assured" tab where Q1=Apr+May+Jun, Q2 = Jul+Aug Sep and so on. The Top Commits Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage 4. The Strong Prospect Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage
Currently I am using formulas but there is too much of hard-coding and its becoming difficult to manage as Accounts increase.
I have attached a screen shot below of a sheet I am using to collate fruit orders and calculate an order total for each individual person.
Untitled.png
The price of each fruit item runs along row 3 and then each persons order has its own row. The total price for each person is then calculated in column AM. However I am looking to shorten the formula I am using to calculate the total in this column.
I am currently using:
AM4=SUM(C3*C4+D3*D4+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
AM5=SUM(C3*C5+D3*D5+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
As you can see this formula is far too long so I am looking to write it in compressed formula.
I have a workbook with 11 sheets. In 10 first sheets i é use function PRODUCT(B35;$H$2) to calculate the values in each cell.
Then i tried to get the summation of 10 sheets in 11th sheet for every cell as following but i keep getting zero as a result instead of the summation:
Sheet11!B5 =Sheet1!B5+Sheet2!B5+Sheet3!B5+Sheet4!B5+Sheet5!B5+Sheet6!B5+Sheet8!B5+Sheet7!B5+Sheet9!B5+Sheet10!B 5.
I've run into an issue for which I can find no elegant solution. I consider myself a competent Excel user, so it's pretty embarassing...
What I want to do is to sum the values along the 'axes' of the table. In order to fully explain that, I'll give a small example:
Let's say, this is my source data
I'm trying to generate random numbers that will add up to a certain number and am having trouble brainstorming how to do so. For example, I'm trying to get a+b+c=6 with a, b, and c randomly generated to add up to 6.
View 4 Replies View RelatedSay, I have two columns each having unknown number of rows. what would be the vbscript code to get the summation of a column's values
For example,
Two existing columns are A1 and B1. In order to create C1.
Column C = 10/summation(a1)/(b1)
How to do the above operation?
I was trying to assist someone with 3D referencing and summing, but getting stuck on referencing text based sheetnames.
We are trying to sum range A1:A10 in the sheets between the range defined by A1:A2
Now, if the Sheet names were numerically sequenced, eg. Sheet1, Sheet2, etc...then we could use this formula with no problem
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(A1&":"&A2))&"'!A1:A10"),""))
where A1 and A2 housed the numbers 1 and 2, respectively,
but what if the sheets are text based? Is there a way....say the Sheets were named SheetX, SheetY, SheetZ....
I did some searching on the internet and found samples only with the numeric based sheetnames.
Currently I am trying to do a gantt like chart time summation. I'll try to explain this as clear as possible... Right now I have a program that finds the name of a part in a giant list's column and then copies a few of the other columns (priority and hours to complete) from the part names row and pastes them into a new sheet. I then sort all the found data by their priority number. The job that is gets sorted to the top is the next job to be worked on for the machine. So here is what I need to be able to do...
Based on the following example I need to be able to sum for the object bolt on Machine #1 the tenative date that the "bolt" will be completed by. It will take a minimum of 35 hours for the bolt to get off Machine #1 based on its current priority. However, the part: "bolt" needs to go to Machine #2 before being completed. The part will have to wait a hour for "hammer" before being started on machine 2. (Its data for the time it will take complete "bolt" on machine 2 is also stored in the spreadsheet.) Since the bolts priority is higher than the "nail"... the "bolt" will be the next job to be completed. My question is this: Is there a way to tenatively sum the future hours that it will take to complete the bolt and allow it to be open to changes, in case a higher priority part comes into the system?
Machine #1
PRIORITY HOURSNAME123nut212bolt334washer422kazoo
MACHINE #2
PRIORITY
HOURSNAME122screw22hammer31nail
I was trying to use IF and AND conditions to calculate values for a Col. but when I attempt to sum.
It appears that the value is always "0". Below is my test data.
Col. H Col. J
Qty Result
Row 713 0 <-Excel Formula: =IF(AND(H7>20,H7<=50),"1","0")
Row 825 1 <-Excel Formula: =IF(AND(H8>20,H8<=50),"1","0")
Row 960 0 <-Excel Formula: =IF(AND(H9>20,H9<=50),"1","0")
Row 10Total 0 <-Excel Formula: =SUBTOTAL(9,I7:I9) / =SUM(I7:I9)
I have a sheet with about 1500 hundred lines of data (The number always changes but is around there.)
The data consists of numbers separated by date with headers.
What I am looking to do is add the numbers offset by two rows of alike headers and have the result output to Sheet2.
These results will be totaled / Separated by day.. Date identification is listed in a few places on the sheet.
Then, I need to add the numbers offset by THREE rows of alike headers and have the result output to Sheet2. The output sheet will be properly listed by rows and labeled by appropriate header for the total.
I have attached a worksheet example to better conceptualize. Please let me know if further information is needed. I have an idea of somewhat to do, but don't know how to put it all together. This is the last piece to the program which I have been working on for 2 months now.
Objective: I am trying to find consecutive (2 or more) lap time that is above a certain value per lap. Once that is accomplished. I would like to know the summation of these values and the average. These values (summation and average) are not over the entire data, but for the consecutive periods only. Ideally I would like the for n number of laps and lap-time.
(i.e) if threshold is 85 seconds. Please view the attached image and sample spreadsheet.
Lap #
Lap Time
Threshold
2 or more laps
0
118.2
85
118.2
[Code] ........
The formula I used to get the consecutive data above
formula I used is =IF(AND($B2>$D$2,OR($B3>$D$2,$B1>$D$2)),$B2,""). This yielded the values on the last column.
My question is, how can I sum and find the average and summation of consecutive value till blank cell. Using the example above,
I would require the average, ((118.2+92.74)/2) in one cell. I would require the next cell to give me the average, ((87.5+86.5)/2. Also I would require the summations used.
I need a formula which can sum up some value based on some especial pattern which I indicate in the table below:
ABC
12565
21001542
33850
4
5
My question: I want to sum up value from A1, B1, C1, A2, B2, C2. I also need a formula to control how much I want to add. For example, I can instruct the formula to sum A1, B1, C1 and A2 (25+65+0+100) or sum A1, B1, C1, A2, B2, and C2.
Am currently working on an excel sheet that has values in multiple columns and rows. Simple example would be like
Assume two columns , with column One having a dropdown list to select the value ,
Pencil 2
Paper 3
Pencil 3
Eraser 3
Pencil 3
I want the data to be created in another sheet as
Pencil - 8
Paper - 3
Eraser - 3
I understand that this can be done through pivot table but the issue is when I change the quantity of the item in the source , say reduce pencil by 1 , the same should be reflected in the summation sheet as well automatically .
I am working on an inventory report that the owner would like broken out between in-season items and out of season items. Items with no season code are always in season. Items that have seasonality have a 2 digit season code. A as if Jan, B is Feb, etc. A season code might be CE or KB. CE would be in season from March until May. KB is in season from Nov through Feb and out of season the rest of the year.
We are currently in month G. Is there an easy way to use the two digit season code and determine if G falls in between those two months.
workhorse.xlsx
This is part of the bigger excel sheet. I would like to write a macro to find the column D6 based on the location of text 'committed total' . Then addup all numbers from E6 to V6 and write total in W6. Then add d6 and w6 and place it in X6.
Objective: I am trying to find consecutive (2 or more) lap time that is above a certain value per lap. Once that is accomplished. I would like to know the summation of these values and the average. These values (summation and average) are not over the entire data, but for the consecutive periods only. Ideally I would like the for n number of laps and lap-time.
(i.e) if threshold is 85 seconds. Please view the attached image and sample spreadsheet.
Lap #Lap TimeThreshold2 or more laps
0118.285118.2
192.7492.74
284.82
387.587.5
486.586.5
582
681
784.2
887
981
The formula I used to get the consecutive data above
formula I used is =IF(AND($B2>$D$2,OR($B3>$D$2,$B1>$D$2)),$B2,""). This yielded the values on the last column.
My question is, how can I sum and find the average and summation of consecutive value till blank cell. Using the example above, I would require the average, ((118.2+92.74)/2) in one cell. I would require the next cell to give me the average, ((87.5+86.5)/2. Also I would require the summations used.
I'm trying to enter the summation formula into three columns that vary in placement based on the value of num_objs. Basically, the number of grey columns in each of the three sets will equal num_objs. I want the user to be able to enter values into the grey columns in the middle section and the values of the two rightmost white columns (with '0's in them currently) will change accordingly.
My current code is below. However, I keep getting a compile error saying "Expected: end of statement". I've been searching for a solution online for a couple days now, and I don't see how my code is different than what I've been seeing of other people using variables in their formulas.
Need to solve the formula in the file. Is it possible to solve this in just one move? in that case.
View 2 Replies View RelatedI have a array as
01-12-2008 pump-1 25
01-12-2008 pump-2 28
02-12-2008 pump-1 24
03-12-2008 pump-1 26
03-12-2008 pump-2 38
03-12-2008 pump-3 35
I need sum value as
03-12-2008 pump-1 75(=26+24+25)
02-12-2008 pump-2 if no answer blank
03-12-2008 pump-2 66(=38+28)
I'm trying to create a formula similar to this: =sum(B7:B & 8*A2+3). What I want the formula to do is start in B7 and go to B###. The cell number is based on user input. So if the user enters "24" in cell A2, then 8*24 = 192, 192 + 3 = 195, so the formula would be: =sum(B7:B195). Can I do this in a cell, or do I need to run it in a macro?
View 2 Replies View Relatedi 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.)
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???