If Statement formula issue without picture issue lol

[Szucchari]

Hi all,

Last week I was completely stuck with this concept and Kweaver helped me out with this awesome formula, but since then I needed to modify the formula to add another condition and now I cannot get the formula to make S2 = 0 is the other conditions are "Non-Billable" and "No". Below is what I need the formula to do and the formula I am trying to use. The issue I am having is with the last IF in the formula. atleast I think that is where it is broken.

[TABLE="width: 682"]
<colgroup><col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <col width="120" style="width: 90pt; mso-width-source: userset; mso-width-alt: 4388;"> <col width="64" style="width: 48pt;" span="8"> <tbody>[TR]
[TD="width: 67, bgcolor: transparent"] [/TD]
[TD="width: 100, bgcolor: transparent"]D[/TD]
[TD="width: 110, bgcolor: transparent"]G[/TD]
[TD="width: 120, bgcolor: transparent"]H[/TD]
[TD="width: 64, bgcolor: transparent"]P [/TD]
[TD="width: 64, bgcolor: transparent"]Q[/TD]
[TD="width: 64, bgcolor: transparent"]R[/TD]
[TD="width: 64, bgcolor: transparent"]S[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]AA[/TD]
[TD="width: 64, bgcolor: transparent"]AB[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 67, bgcolor: transparent"] [/TD]
[TD="width: 100, bgcolor: transparent"]Billable / Non - Billable[/TD]
[TD="width: 110, bgcolor: transparent"]Shipment Type[/TD]
[TD="width: 120, bgcolor: transparent"]Electronic / mail[/TD]
[TD="width: 64, bgcolor: transparent"] Page cost [/TD]
[TD="width: 64, bgcolor: transparent"]Search Fee Yes /No[/TD]
[TD="width: 64, bgcolor: transparent"]Postage[/TD]
[TD="width: 64, bgcolor: transparent"]Total Billed amount[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"]Yes[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent"] $ 100.00 [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"] $10.00 [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent"] $ 100.00 [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] $10.00 [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Search Fee[/TD]
[TD="bgcolor: transparent"] $ 10.00 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent, align: center"]#N/A[/TD]
[TD="bgcolor: transparent"] $ 100.00 [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] $10.00 [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Electronic [/TD]
[TD="bgcolor: transparent"] $ 6.50 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 13"]If D3 is Billable and H3 is Mail and Q3 is Yes , then I need S3= P3+R3+$AB$16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 14"]If D3 is Billable and H3 is Electronic and Q3 is Yes , then I need S3= $AB$17+$AB$16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 13"]If D3 is Billable and G3 is CD and Q3 is Yes , then I need S3= $AB$17+$AB$16+R3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 10"]If D3 is Non-Billable and Q3 is No, then S3 = 0[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 10"]If D3 is Non-Billable and Q3 is Yes, then S3 = 10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

=IF(AND(E3="Billable",NOT(ISERROR(FIND("Mail",I3))),R3="Yes"),Q3+S3+$AC$18,IF(AND(E3="Billable",NOT(ISERROR(FIND("Electronic",I3))),R3="Yes"),$AC$18+$AC$19,IF(AND(E3="Billable",NOT(ISERROR(FIND("CD",H3))),R3="Yes"),$AC$18+$AC$19+S3,IF(AND(E3="Non-billable",R3="No"),0,10))))

 

[Eric W]

In the original thread I posted a much shorter formula, which I still thinks should work better for you:

	D	G	H	P	Q	R	S	AA	AB
Billable / Non-Billable	Electronic / Mail	Page Cost	search Fee Y/N	Postage	Total Billed
Billable	Mail	Y
Billable	Electronic	Y
Non - Billable	Mail or electronic	N
Non- Billable	Mail	Y
Search fee
Electronic

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><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"][/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S2[/TH]
[TD="align: left"]=IF(D2="Billable",IF(H2="mail",P2+R2,$AB$17),0)+IF(Q2="Y",$AB$16,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Szucchari]

Thanks Eric

 

[Eric W]

You're welcome.

 

[Szucchari]

Hi Eric,

I have been working on this all last week and I still cant get this to work, I am not sure why. If D2="Non-billable" and Q2 "N", I cannot get S2 = 10. please let me know if I am missing something obvious.

 

[Eric W]

If D3 is Non-Billable and Q3 is No, then S3 = 0

I don't understand, from your first post you say that this situation should be 0. Why should it be 10 now?

 

[Szucchari]

I am so sorry, I ment if Q3 is Y,

it will not S3 will not =10, it stays at 0

 

[Eric W]

Are you actually using the letter Y, or the whole word "Yes"? If the latter, try:

=IF(D2="Billable",IF(H2="mail",P2+R2,$AB$17),0)+IF(LEFT(Q2)="Y",$AB$16,0)

which just checks for the left letter of the word. If that's still not it, please show me the formula you're using, the data on the row you're checking, and the expected output.