=IF Help

[tandkb]

Hi,

Can someone tell me why my If statement is not working.

=IF(A5=B4,1,SUMPRODUCT(($A5&$A$4=Transfers!$G$5:$G$5000&Transfers!$C$5:$C$5000)*(B$4=Transfers!$F$5:$F$5000)*(Transfers!$J$5:$J$5000)))/SUMPRODUCT(($B$4&$A$4=Transfers!$F$5:$F$5000&Transfers!$C$5:$C$5000)*(B$4=Transfers!$F$5:$F$5000)*(Transfers!$J$5:$J$5000))

In this case A5 does equal B4 but it is not putting 1 in the cell. A5 and B4 are both words and they are exactly the same so it should be putting a 1 in the cell but it is just running the forumla after the , Any help would be greatly appreciated.

Thanks!

 

[JoeMo]

One possibility is that A5 and B4 look like they are the same but one or both have some extra spaces. In any empty cell enter: =EXACT(A5,B4)
If that returns False try: =EXACT(TRIM(A5),TRIM(B4))

if that returns True one or both cells have some extra spaces. In that case you can use the TRIM functions in your formula or you can remove the spaces.

 

[DanteAmor]

Rewrite the same data in both columns.
Check that they do not have spaces before or after the word.
Confirm with this formula:

=exact(A5,B4)

 

[tandkb]

I used the exact forumla and returns true. Hmmm.

 

[Michael M]

What ARE the values in A5 and B4 ??

 

[DanteAmor]

This part of the formula is outside the If, ​​if the first one is met, return 1 but then it is divided by the result of the formula.

/SUMPRODUCT(($B$4&$A$4=Transfers!$F$5:$F$5000&Transfers!$C$5:$C$5000)*(B$4=Transfers!$F$5:$F$5000)*(Transfers!$J$5:$J$5000))

 

[tandkb]

Ah, Is there a way to make is where that is not outside of the the If,?

Thanks so much!

 

[DanteAmor]

Try this

=IF(A5=B4,1,(SUMPRODUCTO(($A5&$A$4=Transfers!$G$5:$G$5000&Transfers!$C$5:$C$5000)*(B$4=Transfers!$F$5:$F$5000)*(Transfers!$J$5:$J$5000)))/SUMPRODUCT(($B$4&$A$4=Transfers!$F$5:$F$5000&Transfers!$C$5:$C$5000)*(B$4=Transfers!$F$5:$F$5000)*(Transfers!$J$5:$J$5000)))

 

[tandkb]

Worked Perfect! Thanks again so much!

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.