IF Cell G2=Payment and Cell H2 equals what is in another sheets range (A1-A41) then I2+J2 otherwise 0.00 Need for Payment, Overpayment, Credit and Re

[DennisYoung]

I am very new to formula's so please be kind.

I am doing a formula for Credit Card types and if it is a Payment or Overpayment or Credit or Return then add cells I2 (PaymentAmnt) and J2 (Subtotal) to cell R2

I have tried =IF(OR(EXACT(G2="Payment",H2=CCardTypes!$A$1:$A$41)),I2+J2,"0.00") and it gives me data but when I add the second nest =IF(OR(EXACT(G2="Payment",H2=CCardTypes!$A$1:$A$41)),I2+J2,IF(OR(EXACT(G2="Overpayment",H2=CCardTypes!$A$1:$A$41)),I2+J2,"0.00")) it puts amounts into all the cells even if it is not on the Credit Card list

 

[6StringJazzer]

To start, your syntax for EXACT is not correct. EXACT takes two text arguments, and returns TRUE if they are exactly the same. I do not know what you are intending when you use it like this:

EXACT(G2="Payment",H2=CCardTypes!$A$1:$A$41)

Let's get that cleared up first then see if there are other problems.

 

[DennisYoung]

Like I said I am very new to formulas in general. I have no clue what I am doing which is why I am here.

So, lets go with what I need

If G2 equals payment and H2 equals anything in the Credit Card list I need to Add I2 and J2 and put in R2
If G2 equals overpayment and H2 equals anything in the Credit Card list I need to Add I2 and J2 and put in R2
If G2 equals credit and H2 equals anything in the Credit Card list I need to Add I2 and J2 and put in R2
If G2 equals return and H2 equals anything in the Credit Card list I need to Add I2 and J2 and put in R2
otherwise 0.00 in R2

Not sure what you mean clear that up, all I know when I put in EXACT I do get some data without it not so much

 

[6StringJazzer]

Use this formula in R2. You are using a table so it should fill down automatically.

=IF(ISERROR(MATCH(H2,CCardTypes!$A$1:$A$41,0)),0,IF(OR(G2={"Payment","Overpayment","Credit","Return"}),I2+J2,0))

 

[DennisYoung]

@6StringJazzer Thank You, Thank You, Thank You! This worked perfectly. I learned more today than I have in the last month.

 

[6StringJazzer]

Glad it worked, since I didn't have data to test it

Reading your logic it was clear that in every case, the credit card had to be found. That is what the MATCH function does. I also showed a shortcut for comparing a cell to see if it matches any of several values.

Come to think of it this is the equivalent logic and is just a little simpler.

=IF(AND(ISNUMBER(MATCH(H2,CCardTypes!$A$1:$A$41,0)),OR(G2={"Payment","Overpayment","Credit","Return"})),I2+J2,0)

 

[DennisYoung]

One more question if I may?

How would you do one that does not match the list? Same scenario but add the same cells if there is not a match?

 

[6StringJazzer]

In that case you are going to add the same numbers if it is in the list, or if it is not in the list. Therefore it doesn't matter if it's in the list. You just remove that condition.

=IF(ISNUMBER(MATCH(H2,CCardTypes!$A$1:$A$41,0)),I2+J2,0)