index/ match formula, i think

[belfast]

Hello
I have a top row of column headings but the column headings may be the same - a, b, c, a, a, b. etc. In the columns below the column headings are many blank cells and some with numbers, randomly distributed. I need a formula which sums all the numbers in rows which have 'a' as a column heading. I think it's an array of some sort or a combination of index and match.

I hope this is clear enough. I would appreciate any help

 

[Aladin Akyurek]

On 2002-09-12 14:00, sen_edp wrote:
Hello Belfast

did you notice my post above ?

Since you're asking... maybe also less expensive...

Book8
	A	B	C	D	E	F	G	H
1	a	b	c	a	b	a	Totals	a
2	1		2	1	3	5	12	12
3		2	3		3		8	0
4	3	3			3	4	13	13
5		4	5		3		12	0
6							45	25
Sheet1

Aladin

 

[belfast]

Yes Aladin that works nicely although like the other approach using SUMPRODUCT, it seems to run into difficulties when there are a lot of columns and several entries in a row. Any thoughts
Belfast

 

[sen_edp]

hello Belfast

I tried an example with the sumproduct way
with 20 columns and 2000 rows and i got no
problem .
if you get #value error , something's wrong with your data, maybe you have text somewhere
or spaces.

hth

 

[belfast]

It's not really the number of rows or the number of columns that is the problem. It's when there is 8 or 9 numbers in a row
Belfast

 

[belfast]

Sorry again
You were right there was some thing wrong with data in one column
Thanks again
Belfast