index/ match formula, i think

belfast

New Member
Joined
Sep 11, 2002
Messages
15
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
On 2002-09-12 01:41, belfast wrote:
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

Let A1:E1 house the labels/column headings like "a", "b", etc.

Let A2:E2 house the values to be conditionally summed.

=SUMPRODUCT(($A$1:$E$1="a")+0,A2:E2)

should give you the desired sum.
 

belfast

New Member
Joined
Sep 11, 2002
Messages
15
Sorry
I have not made it clear enough. There are numbers and blanks in a number of rows below the column headings and not just the one immediately below
 

belfast

New Member
Joined
Sep 11, 2002
Messages
15
Sorry
I have not made it clear enough. There are numbers and blanks in a number of rows below the column headings and not just the one immediately below
 

belfast

New Member
Joined
Sep 11, 2002
Messages
15

ADVERTISEMENT

Sorry
I have not made it clear enough. There are numbers and blanks in a number of rows below the column headings and not just the one immediately below
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

On 2002-09-12 05:53, belfast wrote:
Sorry
I have not made it clear enough. There are numbers and blanks in a number of rows below the column headings and not just the one immediately below

You mean a larger range to sum... That requires changing the shape of the formula...

=SUMPRODUCT(($A$1:$E$1="a")*(A2:E6))

Or, if you have "" in the range to sum:

=SUMPRODUCT((A1:E1="a")*ISNUMBER(A2:E6),A2:E6)

where A2:E6 is the range to sum.
 

belfast

New Member
Joined
Sep 11, 2002
Messages
15
Hi there
thanks for your help so far. We are getting closer but i'm still not making myself clear. I'm not wanting to sum the columns of those which have a column heading of "a". I want to sum the ROWS of all those non-empty cells whose column heading is "a". In Andreas' example the sum would be the sum of all the numbers as every row has a cell whose column heading is 'A'. Don't give up on me please
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
On 2002-09-12 07:22, belfast wrote:
Hi there
thanks for your help so far. We are getting closer but i'm still not making myself clear. I'm not wanting to sum the columns of those which have a column heading of "a". I want to sum the ROWS of all those non-empty cells whose column heading is "a". In Andreas' example the sum would be the sum of all the numbers as every row has a cell whose column heading is 'A'. Don't give up on me please

What should be the result according to you if we use the data Andreas posted as example?
 

thelton

New Member
Joined
Sep 5, 2002
Messages
29
Just trying to help

I think he would want the result of row 2 with column heading 'a' to equal 7
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,497
Members
412,670
Latest member
Khin Zaw Htwe
Top