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

 

[belfast]

The answer in Andreas example would be 58.
If you'll bear with me I don't think I,ve asked the question very well. Can I try again?

I have a set of data of around 100 rows with column headings in row 1. The column headings may be the same eg a, b, c, a, a , c. In the cells below each column header there are many blank cells and some numbers, randomly distributed. At the extreme right I have a column which sums the numbers in each row. I need a formula which sums those row totals in the right hand column whose rows contain one or more nonblank cells with a column header of 'a'.
I hope i have explained that prperly . I would appreciate any help

 

[Aladin Akyurek]

On 2002-09-12 13:06, belfast wrote:
The answer in Andreas example would be 58.
If you'll bear with me I don't think I,ve asked the question very well. Can I try again?

I have a set of data of around 100 rows with column headings in row 1. The column headings may be the same eg a, b, c, a, a , c. In the cells below each column header there are many blank cells and some numbers, randomly distributed. At the extreme right I have a column which sums the numbers in each row. I need a formula which sums those row totals in the right hand column whose rows contain one or more nonblank cells with a column header of 'a'.
I hope i have explained that prperly . I would appreciate any help

If I just sum all the numbers in the Andreas data, the result is 59. How did you arrive at 58?

 

[belfast]

Sorry it is 59

 

[Aladin Akyurek]

On 2002-09-12 13:31, belfast wrote:
Sorry it is 59

So you're summing up everything... I don't think I understand what the problem is... Can you give yourself a small example along with the expected result?

 

[sen_edp]

hello all

Book1
	A	B	C	D	E	F	G	H
1	a	b	c	a	b	a	totals	totalswithcondition
2	1		2	1	3	5	12	12
3		2	3	1	3	3	12	12
4	3	3			3	4	13	13
5	4	4	5	1	3	5	22	22
6							59	59
7
8
9
10
11	a	b	c	a	b	a	totals	totalswithcondition
12	1		2	1	3	5	12	12
13		2	3		3		8	0
14	3	3			3	4	13	13
15		4	5		3		12	0
16							45	25
Sheet1

I am sure Aladin has a better way to do it,

hth

 

[belfast]

I hvaent cracked how to send examples yet but if you refer to Andreas's example, if cells A4 and F4 were blank the answer would be 46. You only sum those rows which have a nonblank cell in a row which has 'a' at the top.
Keep trying please

 

[belfast]

I haven't cracked how to send examples yet but if you refer to Andreas's example, if cells A4 and F4 were blank the answer would be 46. You only sum those rows which have a nonblank cell in a row which has 'a' at the top.
Keep trying please

 

[belfast]

Yes Andreas
I think that's it. Than you very much indeed. Thanks too, to Aladin. I was not making it very clear.
Belfast

 

[sen_edp]

Hello Belfast

did you notice my post above ?

 

[kskinne]

how about this - assuming that your column headings are in cells A1:F1, place this formula in cell G1:

=SUMIF($A$1:$F$1,"A",A2:F2)

which will total all the cells in A2:F2 that contain an "A" in the column heading. copy this formula down column G to the last row of your data. then you can total column G to get you total for all rows. is this what you are trying to do? if not, hope you figure it out - if anybody can it's probably Aladin

HTH
kevin