IF Statement-Multiple Criteria to Change Results in a Column

[smurphy208]

Hello-
I have a customer transaction spreadsheet that I export out of QuickBooks and it looks mostly like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A -Customer Name[/TD]
[TD]Column B-Type[/TD]
[TD]Column F-Amount[/TD]
[/TR]
[TR]
[TD]01-ABC[/TD]
[TD]Invoice[/TD]
[TD]50.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Payment[/TD]
[TD]50.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Invoice[/TD]
[TD]100.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Invoice[/TD]
[TD]50.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Payment[/TD]
[TD]50.00[/TD]
[/TR]
</tbody>[/TABLE]






I'm looking for any suggestions that will look up what is in Column B and if it says "Payment" it will make the value in Column F be a negative. Example Payment of 50.00 would show as -50.00. If it says "Invoice" then it would leave the value as is.

I can get an IF formula to do one or the other but not both together. =IF(B4="Payment",-F4)
I'm sure this is super easy but I can't seem to figure it out, lol.
Thank you so much for your help!

 

[nardagus]

Of course you can nest IFs:

=IF(B4="Payment",-F4,IF(B4="Invoice",F4,0))

P.s. You can also create a dictionary to use with other formulas, but this would be helpfull if you had more cases than 2.

 

[smurphy208]

THANK YOU!!!!
I knew it was something simple like a coma in the right location!!!
Not sure what you mean by a dictionary, though. My spreadsheet is rather large but I can fill series the formula.
Thanks again so very much!

 

[nardagus]

You're welcome.

P.s. Dictionary is a "side table" where you can enter information about what do you want to find, and then what value you want to get in return.

I.e. You have only two cases here: INVOICE and PAYMENT. What if there was a 3rd option? INVOICE CORRECTION (this is how it is called in english right? ) cause of prices miscalculation or client returns goods. In this case you would need 3 IFs nested. With a dictionary you don't need that. Just do this:

	A	B	C	D	E	F	G	H	I
Customer	Type	Price	Proper value	Type	Sign
Cust1	invoice	invoice
Cust2	payment	payment	-
Cust3	correction	correction	-

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=INDEX($I$2:$I$4,MATCH(B2,$H$2:$H$4,0))&C2[/TD]
[/TR]
[TR]
[TH]D3[/TH]
[TD="align: left"]=INDEX($I$2:$I$4,MATCH(B3,$H$2:$H$4,0))&C3[/TD]
[/TR]
[TR]
[TH]D4[/TH]
[TD="align: left"]=INDEX($I$2:$I$4,MATCH(B4,$H$2:$H$4,0))&C4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]