If Statement formula issue without picture issue lol

S

Szucchari

New Member
Joined
Jun 3, 2019
Messages
22
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))))
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
In the original thread I posted a much shorter formula, which I still thinks should work better for you:

DGHPQRSAAAB
Billable / Non-BillableElectronic / MailPage Costsearch Fee Y/NPostageTotal Billed
BillableMailY
BillableElectronicY
Non - BillableMail or electronicN
Non- BillableMailY
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]
 
Upvote 0
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.
 
Upvote 0
If D3 is Non-Billable and Q3 is No, then S3 = 0
Click to expand...


I don't understand, from your first post you say that this situation should be 0. Why should it be 10 now?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

HappyChappy
  • Question Question
Hours over basic
Replies
4
Views
402
Fluff
Fluff
3
  • Question Question
VBA - Input cell formula from another cells value?
Replies
4
Views
854
30136353
3
itsrich
  • Solved
INDIRECT, can I use it in these formulas?
Replies
5
Views
441
Fluff
Fluff
E
  • Solved
shorten 2 divide formulas
2 3
Replies
24
Views
1K
excelNewbie22
E
J
  • Solved
trending value projections without counting sundays
Replies
2
Views
298
KRice
K

Forum statistics

Threads
1,224,821
Messages
6,181,163
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top