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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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
 

sen_edp

Well-known Member
Joined
Mar 13, 2002
Messages
555
Office Version
  1. 365
Platform
  1. Windows
Hello
BOOK2
ABCDEF
1abcaba
212135
323133
43334
5445135
6
7
8
928
Sheet1


hth
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210

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,210
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,501
Messages
5,832,088
Members
430,110
Latest member
Chyke_mxl

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
Top