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.
1 2 3 4 5 6 7 8 9 10
The function would return: 1+2+3+4+5 = 15
0 0 0 4 5 6 7 8 9 10
Function would return: 4 + 5 + 6 + 7 + 8 = 30
0 0 0 0 0 0 0 8 9 10
The function would return: 10 + 9 + 8 = 27
I have had success with:
(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.
I need to sum the items in column G12:G39 with the 1st name in Column B12:B39 which could be 1 of 9 different names, that, and the 2nd name in column D12:D39 cannot not say either #4 or TE. If it says #4 or TE, then it needs to sum the info in colmn G12:G39, based on the 1st name in column B12:B39.
I am looking for help in developing steps in a spreadsheet to calculate a sum based on a few conditions. The basis of my spreadsheet works fine, however I am trying to add the contents of a cell to another by checking other cells.
Cells A1:A10 contain a numerical sequence (1-10 respectively) to indicate a "Node"
Cells B1:B10 contain a user selected number (1-10) which defines the Node it is fed from. We'll call this "Fed From Node".......ie. If Cell B2=1, that means that Node 2 is fed from Node 1.
Cells C1:C10 contain a value which is input by the user. We'll call this "Amps"
Cells D1:D10 would show the total "Amps" of the current cell added to all others that are fed from same.
What I am ultimately trying to figure out is how to add the "Amps" to each "Node" and show the results in column D.
More specifically if the spreadshet looks like the following: ...
I have been trying to search an array and cannot find the correct combination of functions to get the information I want. I might need a macro to do this but I will try anything. Here is an example:
1 A 1 1 B 2 2 A 3 2 B 4 3 A 5 3 B 6
I want to search for 3 and A and return the value in the 3rd column. I have tried several functions but cant figure out how to make sure I get the correct value in the 2nd column that corresponds to the value in the 1st column.
I could probably fix by making a loop, but I am trying to avoid doing any more looping. The program that I am working on has way too much looping in it already, and is getting kind of bogged down.
Here is what I need. On my worksheet, column D lists the names of farms, column E lists a specific chicken house on that farm (by number), and column F lists how many chicks are being placed into that house. What I need to do is find a way, within VBA, to add up all of the chicks in column F that correspond to a certain farm name (column D) and chicken house (column E).
I can make something like this work in excel, but not in VBA. For example, if I wanted to find out how many chicks were being delivered to Johnson Farms, house 4, I would use this formula:
I have a cell that I need to make a calculation if certain other cells =Y, or to show "0" if the other cells =N. I can do the calculation for the Y part but how do I add the N part of it? Here is what I have:
I know there have been many posts about multiple conditions in arrays, but I didn't find one that applied to my situation, so I'm hoping can help me out of jam. I'm using Excel 2000 on XP.
I have a database of sales information. Customer name is listed in column C, and columns F through Q contain the sales for the months of July - June (fiscal calendar year), with the headers for the months in row 4. There can be many rows of sales for the same customer, hence the need to sum them.
C F G H CUSTOMER JULY AUGUST SEPTEMBER customer1 2 3 4 customer1 2 3 4 customer2 2 3 4 customer2 2 3 4
The problem is that this information will need to be updated every month to reflect sales year-to-date, meaning that the conditions for summing the data will change depending on how far into the year we are. So for instance, in August I will need to sum all the records for customer1 in the July column (column F) and the August column (column G), but next month it will have to sum the records for customer1 from July, August, and September (col H).
The result of this information would be displayed on a different worksheet. My thought was to assign a range of 12 cells (Sheet2!A1:A12) and to populate those cells with any of the months that need to be summed thus far. So for example, through September, A1 = "July", A2 = "August", and A3 = "September". Then the formula would use those values to evaulate the conditions. So in plain English the formula would:
sum the values in columns F : Q where the monthly header = the values in the range Sheet2!A1:A12 for all records where CUSTOMER = CustomerName
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:
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?
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.
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 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 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.
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 simple sumif formula that says =SUMIF(W61:W112,"<30",J61:J112). In column W, there are values ranging from 0 to 5000. If the formula is written like it is above, it excludes summing values from column J when the cell in column W equals 0. Why is this? I can just add another function that says sumif "=0", but I don't think I should have to.