If/AND/SUM function with multiple cells and criteria

[Wolfieee]

Hi,

I am trying to sum up 3 cells. This issue is I want to have them sum when specific text is added into two other cells. Here is the formula I have tried.

=IF($AJ6="M",SUM($AG6:$AH6),IF($AJ6="B",SUM($AF6-($AF6*0.2), $AG6:$AH6), IF(AND(AI6="P",$AI6="N",$AI6="B",$AJ6="M"), SUM($AG6:$AH6,500), IF(AND($AI6="P",$AI6="N",$AI6="B"), SUM($AF5:$AH5,500),IF(AND($AI6="",$AJ6=""),SUM($AF6:$AH6),"")))))

I most of it works. it's when it gets to the IF(AND that it will not work. The last one IF(AND($AI6="",$AJ6=""),SUM($AF6:$AH6) work fine but the other two will just not go. I just can't work out what I'm doing wrong. Any help would be great.

Thanks in advance

 

[tryharder1997]

Have you tried a sumif formula instead and also is the text just those letters or are they letters starts to words?

 

[Finalfight40]

I would say its these parts:

IF(AND(AI6="P",$AI6="N",$AI6="B",$AJ6="M")

IF(AND($AI6="P",$AI6="N",$AI6="B")

AI6 can not be equal to all P and N and B.

Did you mean to use an OR statement in there as well maybe?

 

[Wolfieee]

Not sure if this helps

AF5=$
AG5=$
AH5=$

AI5= Will have either B, N or P entered. If any of these are entered it needs to add $500
AJ5= Will have M or B in it - If M is entered it will need to add AG5 and AH6 together but not AF5. If B is entered it will need to take 20% off AF5 and then add AG5 and AH5 to the total

if nothing is entered in AI5 then it needs to still do what AJ5 is doing and visa versa

if nothing is entered in both AI5 and AJ5 then it needs to sum up AF5 to AH5

The letters will end being words but for testing, it was easier just to use letters

It did try the Sumif formula but couldn't get it to work.

and yes maybe I should try an OR statement.

It's late maybe my brain is just not working

 

[Wolfieee]

Would you believe the OR statement worked?

Just need to make it a shorter formula now.

Maybe I should stop looking at this screen and get some sleep.