Sum of first n from m cells

[jshah]

Hi,

Using excel formulas, how do find an sum of first n values of an array? Each cell in the array may or maynot contain a number, ignore non-numbers in an array? If n is greater than count of number then add all numbers present in the array.

In each case I would like to find the count of numbers that were added.

example
The array (column) contains numbers OR a letter "Y". eg
1,45,Y,32,56,Y,12,Y,Y,16,18.

if n = 2 ,then I want to calculate sum of 1 and 45, and count to return 2.

if n = 3 , then I want to calculate sum of 1,45 and 32 (ignoring "Y") and count to return 3.

if n = 7 ,then I want to calculate sum of 1,45,32,56,12,16,18 (ignoring "Y") and count to return 7.

if n = 9 ,then, I want to sum of 1,45,32,56,12,16,18 (ignoring "Y") and count to return 7 (in this case return count is < n).

if possible I do not want add any extra rows or coloums in the spread sheet to store intermediate data.

Thanks for the help.

 

[Aladin Akyurek]

=SUM(OFFSET($A$2,0,0,E2))

=COUNT(OFFSET($A$2,0,0,E2))

where E2 houses a value like 2 (or n = 1,2,3,...).

 

[jshah]

Thank you very much.
Perfect answer in a few mins of asking the question, much appreciated

 

[Peter_SSs]

Thank you very much.
Perfect answer in a few mins of asking the question, much appreciated

In that case, did you ask the wrong question or give incorrect examples?

if n = 3 , then I want to calculate sum of 1,45 and 32 (ignoring "Y") and count to return 3

This sum would be 78 and you have stated the count should be 3. However, the suggested formulas (E6:H7 below) return 46 and 2 for this example.

I know you said you didn't want any extra columns but I have used one. If it is to be removed, I think the remaining formulas would become much more complicated.

Anyway, I think with formula in D1, copied down, and formulas in E3:E4, copied across, you get the results indicated in your examples.

Excel Workbook
	A	B	C	D	E	F	G	H
2	1			1	2	3	7	9
3	45			2	46	78	180	180
4	Y			2	2	3	7	7
5	32			3
6	56			4	46	46	146	146
7	Y			4	2	2	5	5
8	12			5
9	Y			5
10	Y			5
11	16			6
12	18			7
Sum first n

 

[jshah]

Hi peter,
Thank you very much, i agree the original answer suggested by Aladin Akyurek is not correct as the per the question.

Is there a way I can getting the answer without creating the "D" column in your answer. My spread sheet has over 1000 rows and I would like if possible, to find answer which does not involve creating of extra row for every row I have in the table.

Never the less, I appreciate, you sending a correction and sugguestion a solution that works in practice.

thanks
jshah

 

[Peter_SSs]

Hi peter,
Thank you very much, i agree the original answer suggested by Aladin Akyurek is not correct as the per the question.

Is there a way I can getting the answer without creating the "D" column in your answer. My spread sheet has over 1000 rows and I would like if possible, to find answer which does not involve creating of extra row for every row I have in the table.

Never the less, I appreciate, you sending a correction and sugguestion a solution that works in practice.

thanks
jshah

The formula in E3 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across.

Excel Workbook
	A	B	C	D	E	F	G	H
1
2	1			n:	2	3	7	9
3	45			Sum:	46	78	180	180
4	Y			Count:	2	3	7	7
5	32
6	56
7	Y
8	12
9	Y
10	Y
11	16
12	18
13
Sum first n

 

[jshah]

Hi Peter,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
thank you. Superb use of array formulas.I did not know of their existence. I am decomposing your suggested answer to unravel the working.
JShah<o></o>