=IF Help

tandkb

New Member
Joined
Dec 29, 2010
Messages
37
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,645
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,937
Office Version
2007
Platform
Windows
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)
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,975
Office Version
2013
Platform
Windows
What ARE the values in A5 and B4 ??
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,937
Office Version
2007
Platform
Windows
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

New Member
Joined
Dec 29, 2010
Messages
37
Ah, Is there a way to make is where that is not outside of the the If,?

Thanks so much!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,937
Office Version
2007
Platform
Windows
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)))
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,937
Office Version
2007
Platform
Windows
Im glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,078,486
Messages
5,340,616
Members
399,387
Latest member
amrita34

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top