# index/ match formula, i think

#### belfast

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

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.

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

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

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

Hello
BOOK2
ABCDEF
1abcaba
212135
323133
43334
5445135
6
7
8
928
Sheet1

hth

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.

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

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?

Just trying to help

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

Replies
3
Views
300
Replies
3
Views
226
Replies
7
Views
262
Replies
6
Views
271
Replies
6
Views
372

1,216,487
Messages
6,130,943
Members
449,608
Latest member
jacobmudombe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back