Hi,

I have to sumup the numbers apprearing in various columns against column
headers in a range. say for example I have columns A to D and rows 1 to 4. so
I have four column headers against which there can be values in any of the
columns B,C and D.

I am using the below formula in cell E1 but the result in always ZERO.

=sumif(A1:D4,A1,A1:D4)

I wonder it must be very easy but not able to krack it. Kindly help.

Thanks
MA

That formula does exactly what you've asked it to do.

It sums those cells in the defined range where the contents are equal to A1.
The formula =sumif(A1:D4,A1,A1:D4) could be simplified to =SUMIF(A1:D4,A1),
and you'd get the same result from =COUNTIF(A1:D4,A1)*A1

If you are trying to do something different, perhaps you can explain what
you wanted to do.
--
David Biddulph

"MA" wrote in message
...
Hi,

I have to sumup the numbers apprearing in various columns against column
headers in a range. say for example I have columns A to D and rows 1 to 4.
so
I have four column headers against which there can be values in any of the
columns B,C and D.

I am using the below formula in cell E1 but the result in always ZERO.

=sumif(A1:D4,A1,A1:D4)

I wonder it must be very easy but not able to krack it. Kindly help.

Thanks
MA

Hi David,

Thanks for your response. I do understand what you said. I would like to
explain my requirement in further detail.

I have several defined ranges on various sheets in a file. The first column
of each denfined range is expense head (text) and subsequent columns contains
either numbers or are blank. I wish to sum all the numbers against a
particular expense head separately in a sheet (used for reporting total
numbers against each head of expense). I have listed all the expense heads in
a column and used the sumif formula which i mentioned in my below example
against each of those expense heads. As i am using range names as reference
in the formula i am not in a position to define the Sum_range separately in
the syntax. Hope above clarifies about my requirement.

Kindly provide your expert advice to resolve this.

thanks
MA

"David Biddulph" wrote:

That formula does exactly what you've asked it to do.

It sums those cells in the defined range where the contents are equal to A1.
The formula =sumif(A1:D4,A1,A1:D4) could be simplified to =SUMIF(A1:D4,A1),
and you'd get the same result from =COUNTIF(A1:D4,A1)*A1

If you are trying to do something different, perhaps you can explain what
you wanted to do.
--
David Biddulph

"MA" wrote in message
...
Hi,

I have to sumup the numbers apprearing in various columns against column
headers in a range. say for example I have columns A to D and rows 1 to 4.
so
I have four column headers against which there can be values in any of the
columns B,C and D.

I am using the below formula in cell E1 but the result in always ZERO.

=sumif(A1:D4,A1,A1:D4)

I wonder it must be very easy but not able to krack it. Kindly help.

Thanks
MA

I think this is what you have asked for:-

I have given an example which will be easier to you to understand.

Column A Column B Column C Column D
Lion Cat Tiger Elephant
532 325 654 58
533 326 655 59
534 327 656 60
1599 978 1965 177

Just paste this formula anywhere you want:-

=SUMPRODUCT(($A$2:$D$4)*(ISNUMBER(SEARCH($H$1,$A$1 :$D$1))))
In H1 you need to enter the Column Heading to get the Sum of the particular
Column Heading.

Otherwise try this

=SUMPRODUCT(($A$2:$D$4)*(ISNUMBER(SEARCH("Lion",$A $1:$D$1))))
In this formula instead of H1 reference the Column Heading is written in the
formula itself.

If this post helps, Click Yes...

--------------------
(MS-Exl-Learner)
--------------------



"MA" wrote:

Hi,

I have to sumup the numbers apprearing in various columns against column
headers in a range. say for example I have columns A to D and rows 1 to 4. so
I have four column headers against which there can be values in any of the
columns B,C and D.

I am using the below formula in cell E1 but the result in always ZERO.

=sumif(A1:D4,A1,A1:D4)

I wonder it must be very easy but not able to krack it. Kindly help.

Thanks
MA

Hi,

Thanks for your explanation, but I am looking for a altogether different
solution. I think I need to explain it further.

-I have got several sheets which contains data ranges of different sizes but
all start with a column header (text) and all are named.
-Now consider below example of date ranges starting with column headers.

Sheet 1
Range 1
A B C
D E
Ext. Fee Income - Labour 77,503.75 732,646.25
Ext. Fee Income - Subcontract 8,606.35 147,266.54
Ext. Fee Income - Rebillable 12,396.74 100,356.45

Sheet2
Range 2
E F G
H I
Ext. Fee Income - Labour 1345 36549
Ext. Fee Income - Subcontract 47955 146892
Ext. Fee Income - Rebillable 51479 547123


The requirement is very simple, I need to add on all the numbers appearing
in any column against a column header, for example I need to calculate total
amount in all ranges for "Ext. Fee Income - Labour". The result must be
848044 in above example.

thanks for your help.

MA
"MS-Exl-Learner" wrote:

I think this is what you have asked for:-

I have given an example which will be easier to you to understand.

Column A Column B Column C Column D
Lion Cat Tiger Elephant
532 325 654 58
533 326 655 59
534 327 656 60
1599 978 1965 177

Just paste this formula anywhere you want:-

=SUMPRODUCT(($A$2:$D$4)*(ISNUMBER(SEARCH($H$1,$A$1 :$D$1))))
In H1 you need to enter the Column Heading to get the Sum of the particular
Column Heading.

Otherwise try this

=SUMPRODUCT(($A$2:$D$4)*(ISNUMBER(SEARCH("Lion",$A $1:$D$1))))
In this formula instead of H1 reference the Column Heading is written in the
formula itself.

If this post helps, Click Yes...

--------------------
(MS-Exl-Learner)
--------------------



"MA" wrote:

Hi,

I have to sumup the numbers apprearing in various columns against column
headers in a range. say for example I have columns A to D and rows 1 to 4. so
I have four column headers against which there can be values in any of the
columns B,C and D.

I am using the below formula in cell E1 but the result in always ZERO.

=sumif(A1:D4,A1,A1:D4)

I wonder it must be very easy but not able to krack it. Kindly help.

Thanks
MA