Summing First 5 Numbers In Range With Conditions?
Jul 27, 2012
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.
View 3 Replies
ADVERTISEMENT
May 19, 2008
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.
View 9 Replies
View Related
Jan 31, 2009
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.
For example:
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: ...
View 9 Replies
View Related
Jun 1, 2009
I want to sum certain data, which meets two conditions.
My data set contains three columns and a lot of rows. The columns are the following:
PostalCodeDeparture
PostalCodeArrival
PassengersInCar
I want to sum the total number of passengers with departure postal code 5100 and arrival postal code 5110. (and I want to do the same for all other postal code combinations in the data set)
With "SUMIF" I can only include one condition.
View 4 Replies
View Related
Feb 11, 2010
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.
View 14 Replies
View Related
Mar 27, 2007
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:
= SUM(IF(($D$5:$D$25="JOHNSON FARMS") * ($E$5:$E$25=3),$F$5:$F$25 ))
View 9 Replies
View Related
Jul 7, 2009
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:
=SUMIF(J19:J40,"Y",H19:H40)*0.08
View 13 Replies
View Related
Oct 1, 2007
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.
Example
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
View 9 Replies
View Related
Dec 23, 2011
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.
37.9537.9537.9537.9580.6180.6180.6180.61129.38129.38129.38129.38185.85185.85185.85185.851259.691259
.691259.691259.691259.691259.691259.691259.691259.691259.691259.691259.691259.691259.691259.691259
.691259.691259.691259.691259.69104.96104.96104.96104.96104.960.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00
View 9 Replies
View Related
May 14, 2007
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
View 4 Replies
View Related
Feb 23, 2014
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?
View 8 Replies
View Related
Jun 3, 2014
I want to sume numbers that may or may not be in several cells. If I use the function SUM and one of them is missing, I get an error.
Example:
A1=2
A2="empty"
A3=5
I want the sum of A1+A2+A3 to be 5
View 6 Replies
View Related
Apr 10, 2014
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.
View 1 Replies
View Related
Nov 15, 2008
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
00
11
23
36
410
515
621
728
836
945
1055
1166
1278
1391
View 9 Replies
View Related
Dec 21, 2008
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.
View 9 Replies
View Related
Jun 16, 2009
I would need to get this next thing working:
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?
View 9 Replies
View Related
Aug 17, 2013
I have an unusual situation which is best explained by way of an attached example.
In summary adding and subtracting four cells containing integer values outputs an unexpected non-integer value, which should in fact be zero.
The situation is best explained by way of example which I attach to this post : Excel Problem.xlsx
Basically, Cell E1 should be zero.
View 4 Replies
View Related
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?
View 8 Replies
View Related
Aug 27, 2009
Column A: Start Date
Column B: End Date
Column C: Ethnicity Code (letter and number such as B1)
Column D: Hours
What I need to do is work out how many hours each ethnic group did in a specific month.
View 14 Replies
View Related
Jul 17, 2014
Product Group Arg.Braz.Mex.Peru
Red paint Paint5324
Blue paintPaint5684
WallpaperWallpaper6585
LampsFurniture 4631
ChairsFurniture 1645
Green paintPaint1356
SofasFurniture 3358
SUM(Paint,Argentina)SUM(Paint,Brazil)SUM(Paint,Mexico)SUM(Paint,Peru)
SUM(Wallpaper,Argentina)SUM(Wallpaper,Brazil)SUM(Wallpaper,Mexico)SUM(Wallpaper,Peru)
SUM(Furniture,Argentina)SUM(Furniture,Brazil)SUM(Furniture,Mexico)SUM(Furniture,Peru)
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.
View 4 Replies
View Related
Apr 14, 2013
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.
View 9 Replies
View Related
Apr 28, 2014
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.
[URL].....
View 2 Replies
View Related
May 9, 2014
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.
A
B
C
D
1
<0.001
<0.1
0.2
<0.01
2
<0.0001
0.1
0.2
<0.1
3
<0.0001
<0.2
0.2
<0.1
4
<0.001
<0.1
0.2
<0.01
5
<0.0022
<0.5
0.8
<0.22
View 11 Replies
View Related
Sep 1, 2008
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.
View 12 Replies
View Related
Jan 12, 2010
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
View 2 Replies
View Related
Jun 10, 2009
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.
View 9 Replies
View Related
Mar 24, 2009
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)
View 14 Replies
View Related
Jan 6, 2009
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.
View 3 Replies
View Related
Feb 17, 2014
I am trying to create report of the in and outs of tools we are shipping for repair.
Currently in Tab 1
Column F - Date shipped for repair
Column G- Vendor Name
Column H- Date Returned
What I need in Tab B
Vandor - Total Sent- In Repair @ Vendor- Total Repaired - Total Unrepaired
Here are Images if it makes it a little easier to understand
The Info I currently have
image1.png
The info I required filled out based on the previous data
image2.png
View 6 Replies
View Related
Jan 30, 2012
I would like to get the sum of a range in VBA and tried the following that did not work:
S= Sum(Range(Cells(1, 1), Cells(1, 6)))
View 2 Replies
View Related
