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