# Summing Numbers In A Series

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

## Summing Series Of Maximums In Larger Series

Aug 22, 2014

I consider myself decent at Excel, but I don't have quite the depth of knowledge I would like. Here is my issue:

I currently have a series of 50 numbers for each row on a 300 row spreadsheet. I would like to write a formula that identifies the six largest numbers in that series and adds them together. So, for example, if the series was 8-6-4-4-4-5-1-1-0-15-3-9, the formula would spit out 47 (15+9+8+6+5+4)

How do I write that kind of formula?

## Summing Results Of Formula On Series Of Data

Oct 24, 2011

Is there a way to SUM the result of the followig for B4:J4 and B16:J16, the other cell references remain static:

=(B28*(P28^2-IF(ISNUMBER(B16),ABS(B4-B16)^2,B31)))

I a currently using the following which is rather lengthy and not efficient to maintain/edit:

=(B28*(P28^2-ABS(B4-B16)^2))+(B28*(P28^2-ABS(C4-C16)^2))+
(B28*(P28^2-ABS(D4-D16)^2))+(B28*(P28^2-ABS(E4-E16)^2))+
(B28*(P28^2-ABS(F4-F16)^2))+(B28*(P28^2-ABS(G4-G16)^2))+
(B28*(P28^2-ABS(H4-H16)^2))+(B28*(P28^2-ABS(I4-I16)^2))+
(B28*(P28^2-ABS(J4-J16)^2))

## Sine Of An Angle X By Summing An Infinite Series In VBA

Jul 18, 2006

i was set some work related to maths to be carried out in VBA. the problem for me is im finding it very hard to understand either and so i cannot seem to work out how to complete the assignment given.

i can attach the assignment to save space and time reading on here however if you would like me to post up the assignement i can do that too....

## Insert Numbers Based On Missing Numbers In Series

Oct 12, 2010

I have a list of numbers in sequential order. There are numbers missing. Is there a way to have Excel insert rows for the numbers that are missing in the series.

example:

1
2
3
4
8
9

becomes

1
2
3
4
8
9

## Delete Numeric Series Numbers Between Numbers Entered

Mar 14, 2008

I want to ask that I have got a workbook with different number series i want user form where i can enter its start number and end number and then it finds and delete shift cells up said series number i have entered in user form please see mentioned below example.

Series
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
and i want to delete 1 to 5 numbers delete to shift cells up.

## Summing Unique Numbers In A Set Of Reoccurring Numbers

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

## Summing A Row Of Numbers Containing: VALUE

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

## Summing Random Numbers?

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?

## Summing Numbers Even If They Don't Exist?

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

## Summing Every 7 Numbers In Column

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.

## Summing Custom Numbers

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.

## Summing Numbers When Certain Criteria Are Met

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?

## 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

=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.

## Summing Whole Numbers Gives Non Whole Number Output

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.

## 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?

## Summing Numbers With Multiple Criteria

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.

## VBA Code - Summing Groups Of Numbers

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.

## Summing Cells Containing Numbers And Text

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.

## Summing Cells With Text And Numbers

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.

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].....

## Subtracting Until Zero From A Series Of Numbers

Jul 3, 2014

I'm trying to program a spreadsheet for staff attendance at a school. In the spreadsheet the staff member has personal days, sick time, and sick time in the bank (sick time rolled over from last year). When they use sick time, it needs to come out of the bank first, until it reaches zero, then it should move on to the sick time accumulated for this year.

Staff attendance WIP.xlsx

On the left side is the staff name, sick time, personal days, and bank. Then are the school days for July (if a staff member uses sick time/personal day it is placed under the date it was used). On the far right is the total remaining amount that the staff member has left.

The formula I have for cell AF4 is: =MAX(0,G4-SUM(H4:AC4)) so that the cell will not go below zero.

The formula I have for cell AG4 is: =E4-(COUNTIF(H4:AC4,"P")+(COUNTIF(H4:AC4,"HP")/2)) So that when P for personal day is entered, it will take a whole day, and when HP is entered a half a personal day is subtracted.

I need to figure out a formula for cell AD4. I've tried a couple of formulas I've seen on other posts, but they weren't for a series of numbers like this. And when I put them into this particular spreadsheet, it would subtract the bank until zero, move on to the 'sick time' cell and subtract the SUM of all the days sick rather than subtracting what was left when the 'Bank' reached zero.

## Summing Numbers From Multiple Cells With Text?

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

## Differencing Sets Of Numbers, Multiplying, And Summing

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.

## Summing And Searching With Words And Numbers In Different Columns

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

## Returning Characters After A Series Of Numbers

Dec 10, 2013

I have a list of data in Column A of a spreadsheet that is all at different lengths.

I would like to return the numbers from this (see below) seperately and also everything after the last number so returning "green"

Example:

EAST MIDS, YORKSHIRE0112012green
EAST MIDS, YORKSHIRE0112012red
EAST MIDS, YORKSHIRE0112013black
EAST MIDS, YORKSHIRE0112013brown

## Subtract Series Of Numbers From Array

Dec 4, 2012

How can I subtract a series of numbers from an array. example.

Code:
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T

13
14
25
26
37
39

[Code] ......

=ABS(1-B2) C2,D2,E2,F2 AND G and after start again with =abs(2-B2:G2)

I want to know how to do this for example until 60, I mean 60 minus the row B:G

## Series Collection Names Instead Of Numbers?

Aug 30, 2013

I have a graph with something like 40 series that are being plotted. For one, I create non-standard data labels using VBA. In that code, I refer to ChartObjects(1).Chart.SeriesCollection(34). At some point I deleted a series that preceded it in the "select data" list and the code crashed. Took a lot of digging to learn that SeriesCollection(34) had become SeriesCollection(33).

To avoid this in the future, is there a way to refer to the SeriesCollection by a unique name rather than a sequential number?

## Transform Series Of 7 Numbers In 0 - 4 Scores

Apr 5, 2014

I have a series of raws with 7 numbers in each raw; sums may vary a lot from raw to raw, and also differences between one number and the other in a same raw.

Now, i need to obtain a much simpler distribution of scores, so reducing differences.

I would need to transform all numbers into a 0-4 score distribution, according to differences between numbers in a same raw. It's like a kind of rank score.

I want to obtain in each raw:

- 1-2 numbers with score = 4 (if three or more numbers have similar values: no 4 scores)
- a maximum of 3-4 numbers with score 2-3 (according to differences between these numbers but also also according to the 4 scores already assigned)
- all other scores = 1 if the numbers are >0
- all other scores = 0 if the numbers are 0

example: |14|23|3|0|45|0|10| => |2|3|1|0|4|0|2|

Would it be possible to do with Excel?

## Remove Last Digit On A Series Of Numbers

Mar 25, 2004

I have a spreadsheet, with about 1,500 lines. In column A is a list of numbers that I need to remove the last digit from each number, for example in A22 is 02602726521 - I need this to be 0260272652. The numbers are all uniqe.

How can I remove the last digit from every number, without going in manually to do so?

## Summing Up Opposite Numbers In A Column Based On Criteria

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.