Mike, I must be missing some thing again coz i think the last formula would do that
See if I understood you correctly
1. The formula needs to be at cell B4
2. The letters would be either in cell F4 or H4
3. The amount would be in d4
4. Based on letters in cells F4 and H4, you want to put in b4 the value of D4 (positive or negative depending on H4 and F4)
5. In case F4 and H4 are blank, the B4 should be blank
=IF(AND(F4="", h4=""), "", IF( AND(OR(F4="C", F4="P", F4="S", F4="CR", F4="PC", F4="PS"), OR(H4="C", H4="P", H4="S", H4="CR", H4="PC", H4="PS")), D4, -D4) )
1. AND(F4="", h4=""), "" part is saying that if both f4 and h4 are blank, then leave b4 blank too.
2. OR(F4="C", F4="P", F4="S", F4="CR", F4="PC", F4="PS") is checking if F4 contains any of those letters
3. OR(H4="C", H4="P", H4="S", H4="CR", H4="PC", H4="PS") is checking if H4 contains any of those letters
4. IF( AND(OR(...),OR(..)) is saying that both OR condition must be true, that is, both f4 and h4 must have one of those letters
5. , D4, -D4 part is saying that if #4 is true, the b4 = d4 and if #4 is false b4=-d4
I do think that you are telling me that some of those letters means that value should be positive and other letters means negative. Since I dont know which letter mean what, I did not put it. If for negatives, you can just remove them from the list or add one more if clause where -D4 was being flagged in the formula.
If still it does not help, may be you can explain in words what your were trying to do here. =IF(F4="P",D4," ")=IF(G4="P",-D4," ")
I think thats what you were trying to say, but I could be wrong
if F4=p, then d4 else if g4=p then -d4 and if not both then " "