Summing Numbers Only If They Correspond To Another Column?
Mar 15, 2014
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 am trying to find the median of a set of numbers on a column that correspond to dates on the left column. I want a monthly median average of the numbers on the right which correspond to the dates on the left. So for example. I want to make an equation that gives me the median of all the numbers on the right if they fall within the range of a certain month(in this case October). I've tried These:
I 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.
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 a problem in terms of summing unique numbers in a set of reoccurring numbers. Due to the restrictions in the spreadsheet I am unable to use a filter / advanced filter or pivot table. What I basically want to do is sum every time there is a different number. In this case it's:
37.85+80.61+129.38+185.85+1259.69+104.96
The unique numbers have a maximum of 6 and a minimum of 3. Is there a generic formula that I can apply so that it only counts / sums the number once. I can use VBA code or just a simple array. I'm just not familiar with any sort of functions that can do this.
I have a row of numbers that I want to sum. However some of the data could not be retrieved and that means in some cells in the row of numbers contain the message: " #VALUE " and when doing SUM it returns #VALUE
I have a process that generates the value X in cell A1 randomly. So I can change the value of X in A1 by clicking on F9. I want to sum a sequence of X values. I can do this by manually copying each X to a row of cells and then summing the row values. But that is tedious. Can the summation be done automatically without being regarded as being circuler?
The series 1,3,6,10,15,21,28,36 ("A" series) is the ongoing sum of 1+2+3+4+5+6+7+8 ("integers") and naturally goes on for as long as on chooses. I am only interested in the numbers up to 100.
Is there an Excel function that will allow me choose any integer as a starting point and any higher integer as an ending point and provide the sum of the "A" series associated with the two choices.
For example: If I begin at 6 and end on 12 it would give me 308 =28+36+45+55+66+78
I have a list of 100 songs and the artists that sing each song.
What I want is a formula that will look at the rank that it finishes with and assign it a different number (e.g. the song that finished at number 1, has a value of 100).
As there can be multiple songs by the one artist, I need to add them ALL up and have a total score for each singer/band.
e.g.
1 Powderfinger - My Happiness (100 points) 2 U2 - Beautiful Day (99 points) 3 Powderfinger - My kind of scene (98 points) 4 Wheatus - Teenage Dirtbag (97 points) 5 Coldplay - Yellow (96 points)
So Powderfinger would have 198 points and the other 3 would have 99, 97 and 96.
The band names are in one column and the song names are in the next column.
H I J K L M N O P Type No. No.Points Points/RN Points/KR Points/KV Points/ER R/V Total 5 KV 12 4 2 3 4 6 0 8 6 RN 90 18 3 4 5 8 0 26 7 KV 12 4 4 4 5 6 10 13 8 ER 9 KV 10 KR ...and so on, with the following logic
If Type = KV, then P$ = sum(J$,M$,O$), but if it is something else, like RN, then it needs to sum J$,K$,O$
I've tried using the following:
=IF(ISNUMBER(SEARCH("KV";H2));SUM(J2+M2+O2);0;IF(ISNUMBER(SEARCH("ER";H2));SUM(J2+N2+O2)); but only the two first ifs work, which is kind of logical, since the second if is the "value if false" statement. Is there any way to get this to work?
I have a spreadsheet which has data in rows. I need to sum the first 5 values from the left of the range which are greater than zero. BUT if there are less than 5 non zero values present I want to sum from the right of the range.
First condition: 1 2 3 4 5 6 7 8 9 10 The function would return: 1+2+3+4+5 = 15
Second condition: 0 0 0 4 5 6 7 8 9 10 Function would return: 4 + 5 + 6 + 7 + 8 = 30
Third condition: 0 0 0 0 0 0 0 8 9 10 The function would return: 10 + 9 + 8 = 27
I have had success with: =B4+SUM(SMALL(IF(I4:O4<>0,I4:O4),ROW(1:5))) (using ctrl shift enter) BUT it crashes when faced with the third condition.
I would also like to avoid ctrl-shift-enter functions if I can as I don't have much success copying them around the spreadsheet.
Product Group Arg.Braz.Mex.Peru Red paint Paint5324 Blue paintPaint5684 WallpaperWallpaper6585 LampsFurniture 4631 ChairsFurniture 1645 Green paintPaint1356 SofasFurniture 3358
I am trying to work out VBA code or another way in Excel to find a solution for this problem - I need to be able to sum up the values that I have posted below - so for SUM(Paint,Argentina), this would be the sum of the red paint, blue paint and green paint values under the Argentina heading, that are all categorised into the 'Paint' group. The same follows for all the other categories. I was trying to use 'Defined Names' to group the categories together, and so would then do =Sum(Paint). However this was not allowing me to sum by each country, and the 'Create from Selection' naming tool was not naming all of the values for Paint (blue paint, red paint, etc.) under the paint category.
This is an incredibly simplified version of the data I am actually dealing with - I am doing this for 70 country columns and 250 rows of product categories.
I have cell A1:A25. each with a number, and the same text "Hour(s)" So cell A1 would be 24 Hour(s) and so forth down to cell A25.
Due to the sheet being large, I can't just insert another column to list the text. I need for cell A26 to sum the numbers ignoring the test in the cells. I searched to forum, but didn't exactly find anything that works correct.
I do my indoor cricket team stats and I am having some trouble figuring out what formula to use in order to be able to sum a row of numbers where some cells may contain text and numbers or just text.
Please see the attached spreadsheet to understand what I'm talking about.
In the batting tab, when the batter gets out, they lose 5 runs. Outs are noted by St, R, B or C. In terms of runs, the scorers put wides and noballs to the batter on strike which are noted by W or N and this is worth 2 runs. Sometimes the batter on strike will get runs off a W or N so it's noted down as W+2 (which would give 4 runs) etc. So what I need to be able to do in the batting sheet is sum the total runs and count the number of outs.
In the bowling sheet, it's the same story as the batting tab, but I also need to be able to sum the number of extras given and count the number of wickets taken.
I am looking for a formula which will sum numbers with less than symbols in front of them (i.e. they are text cells), and then replace the less than symbol again in the summed cell if initially present. The problem is that not every cell contains a < symbol, some of them are just numbers, and not every column contains a < symbol either.
e.g. I am looking for a formula which if entered in row 5 of this example would give these answers.
I'd like automate consists of entering 56 numbers (up to 3 digits, 000-999) and finding the difference between today's numbers and yesterday's numbers. Then multiplying each difference by a set number (i.e. 1, 2, 3, 5, 10, 20, or 25). These numbers may, from time to time change (i.e. the 32nd entered number may be multiplied by 3 for 2 months, then we might swap out a product and then need to multiply by 5, etc). At the end, I need all 56 differences multiplied by their individual respective multipliers totaled. This needs to be done daily using previous days numbers and be able to change the multiplier without effective previous data.
I was trying to to this myself by Column 1 = multipliers, Column 2 = yesterdays' numbers and Column 3 = today's numbers. The calcualtions are done by excel and a total is summed. This I can do. However, each day i'd need to make a new file and enter yesterday's and today's numbers. This is time consuming. I'd like a system where I can just entere today's numbers and any changes to the multiplier and it will spit out the total.
If anyone can guide me or point me in any direction to accomplish this automation. I would greatly appreciate it. I'm pretty computer savvy, but just have not had the time to dwell into Excel.
For those interested in what this is for: I have a small business that sells instant lottery tickets. Each day we have to figure out the number of tickets sold the previous day. We have a notebook and manually write down numbers, do the substractions by a calculator and total. The table in the notebook is seperated by ticket values (i.e. $1 tickets, $5 tickets, etc). However, certain tickets run out or occasionally we need to move tickets to a different spot, thus the MULTIPLIER in the automated system would need to change.
I'm looking to do the following below: column A would have numbers, column B would have words and a few columns over i would have the total of each same word
column A column B 60house 120car 30misc 52.25food 83.33gashouse 180.25 120.25housecar 120 25miscmisc 55 105foodfood 157.25 50.5gasgas 133.83
I'm a basic excel user really and cannot find a function that will allow me to complete the following:
CELL A1 includes the following sequence of numbers: 1,2,1,0,2,3 I would like to know how to put a formula/function into CELL B1 that sums up the sequence of numbers in CELL A1 (i.e 1+2+1+0+2+3)
We are working on a peer review form using Adobe formscentral (quite handy) It will export to excel which is also handy. The problem is, the questions are not 1-5 but are strongly disagree - neutral - strongly agree. However, we still want to be able to "score" people. How do i make a word like "strongly agree" worth 5 points, so i can get a total and average?
I 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.
I have 4 columns of data, and need to average values that correspond to certain criteria from the other 3 columns. Attached is an example of my data and the desired output. The averages need to come from non-blank cells from Inputs 1 and 2 that match the criteria from the Name column.
A VBA problem has given me a workbook with two sheets. The first one lists the product names and their unit prices, and there is a third column labeled quantity purchased that is blank. The second sheet is an invoice, which will list the product name, the unit price, the number of units sold and the total price of the purchase. Assignment is to create a sub that will automatically fill out the invoice with any number I type into the blank column. Any good code to get two sheets to correspond in that way?
To be more specific, I work with highway signs, and they all have codes. the codes are priced according to their size, sheeting, and aluminum structure.
so codes can be something like : GS-4, IB-12, RB-21, and their sheeting could be: 231, 241, 245S, 420P, and their price is dependent with the size, materials, and quantity ordered. So a quantity of 3 or less of a flat sheet of aluminum, with HIP reflectivity sign would be a 231, where 4 or more of that same sign would be 241.
A workbook has the contracted price, the cells are in order. The order sheet I use is separate from this. But I have to look for each sign for all the info. How can I get the one cell to recognize the Code, along with the Quantity ordered?
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 sheet (like example attached). it's broken out by month for many decades. certain columns like B-N need each field populated. There are others like O, P, Q, where I need to delete all cells except for 2 months that year (in this example, all except May and November).
