links formula

  • Thread starter Thread starter jc
  • Start date Start date
J

jc

I tried to make this formula work but instead
I get an error result "0".

=SUMIF(Billing2!C15:C34,"Building",Billing2!E15:E34)

I would like to reflect the result in Master sheet C2
using A7 ( Building ) as its criteria.

Thanks in advance.


jc
 
Arvi,

So what will i do then to correct this problem? Can you
show me how, step by step.

jc
 
Arvi,

This is how my "Master" Sheet looks like

A B C
1 Charge Code Wk.Exp.
2 Supplies 20.10.940
3 Electrical 20.10.194
4 Telephone 20.10.357
5 Water 20.10.935
6 Tools 20.10.135
7 Building 20.10.972

Wherein i need to reflect expenses of Building from the
"Billing2" sheet using sumif formula:

=SUMIF(Billing2!C15:C34,"Building",Billing2!E15:E34)

c15:c34 (Cost center charge)
e15:e34 (expenses allocated)

Sorry if i make my question too confusing.

jc
 
Hi

To ensure all expenses on Billing sheet are numeric, select range
Billing!E15:E34 and format it as Currency (or General or Number ). Then
select some empty cell and copy it. At last select the range Billing!E15:E34
again, and then PasteSpecial.Add

I tested the formula for Master!C2
=SUMIF(Billing!C$15:C$34,A2,Billing!E$15:E$34)
and then copied it down. It worked for all charges.
Btw., your formula doesn't work when the criteria doesn't match any value in
range Billing!C15:C34 too - maybe you have some additional space or some
typo there.
 
Hi jc

There does not appear to be anything wrong with your formula, provided the
range E15:E34 conatins valid numeric data.

If you are saying you want to use a cell referenc instead of typing the word
"building", then in C2 enter
=SUMIF(Billing2!$C$15:$C$34,A2,Billing2!$E$15:$E$34)
and this will give the total for Supplies
Copy down column C on your master sheet, and then the formula will adjust so
that in C7 you will have
=SUMIF(Billing2!$C$15:$C$34,A7,Billing2!$E$15:$E$34)

Is this what you mean?
 
Arvi,

Thank you so much, now its more clear to me. I think
that's the reason why ive been getting an error result due
to this text format.

jc
 
Roger,

I get your point, but i prefer to use the "text" criteria
from the Master sheet which is "Building" instead of
referring to the cell coordinate in Billing2 sheet.

syntax: (range,criteria,sum_range)
range: from Billing2 sheet
criteria: from Master sheet
sum_range: from Billing2 sheet

Sorry, im not quite good in excel as well as in explaining
what i need.

Hope you understand it.

jc
 
Back
Top