IF formula help

[Snowmakertim]

Hi All,
I am trying to perform the following calculation
If any cell in B=555ABC then add value in H to G14
Your assistance would be greatly appreciated as this seams simple but I cannot for the life of me get the formula caorrect

 

[shemayisroel]

Hi All,
I am trying to perform the following calculation
If any cell in B=555ABC then add value in H to G14
Your assistance would be greatly appreciated as this seams simple but I cannot for the life of me get the formula caorrect

When you say B is that the whole column B or a particular line in B?

 

[Snowmakertim]

Hi, Its the whole column. I have a several differnet cost centres in B that I want to add the $ value in column H to a total in G14. I hope this makes sense

 

[impius]

Hi, Its the whole column. I have a several differnet cost centres in B that I want to add the $ value in column H to a total in G14. I hope this makes sense

This is an array so you must press ctrl+shift+enter

={IF(B1:B10="55abc",$G$14+H1,"")}


Joseph

 

[Snowmakertim]

Hi Joseph, thanks for your help but I cant get this to work. it keeps giving me errors.

 

[shemayisroel]

Hi Joseph, thanks for your help but I cant get this to work. it keeps giving me errors.

It works ok for me

try

=IF(B1:B10="55abc",$G$14+H1,"")

then ctrl+shift+enter

 

[impius]

It works ok for me

try

=IF(B1:B10="55abc",$G$14+H1,"")

then ctrl+shift+enter

right, and make sure you only select the data range for the first argument (ie b1:b10) rather than the whole column.


Joseph

 

[Snowmakertim]

Thanks heaps Joseph

 

[Scott Huish]

You can't compare a whole range to a single value like that. If you mean the entire range must be 55abc, then:

=IF(AND(B1:B10="55abc"),$G$14+H1,"")

Confirm with CTRL-SHIFT-ENTER

or without CTRL-SHIFT-ENTER
=IF(COUNTIF(B1:B10,"55abc")=ROWS(B1:B10),$G$14+H1,"")

 

[impius]

HOTPEPPER is correct if that is what you meant...I did not read the original question that way.

J