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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hello
BOOK2
ABCDEF
1abcaba
212135
323133
43334
5445135
6
7
8
928
Sheet1


hth
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top