SUMIF function

[MA]

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(A14,A1,A14)

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

Thanks
MA

 

[David Biddulph]

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(A14,A1,A14) could be simplified to =SUMIF(A14,A1),
and you'd get the same result from =COUNTIF(A14,A1)*A1

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

 

[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

 

[MS-Exl-Learner]

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...

 

[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