If Statement formula issue without picture issue lol

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.

DGHP QRS AAAB
Billable / Non - BillableShipment TypeElectronic / mail Page cost Search Fee Yes /NoPostageTotal Billed amount Yes
3 #N/A#N/A $ 100.00 0 $10.00 No
4 #N/A#N/A $ 100.00 $10.00 Search Fee $ 10.00
5 #N/A#N/A $ 100.00 $10.00 Electronic $ 6.50
If D3 is Billable and H3 is Mail and Q3 is Yes , then I need S3= P3+R3+$AB$16
If D3 is Billable and H3 is Electronic and Q3 is Yes , then I need S3= $AB$17+$AB$16
If D3 is Billable and G3 is CD and Q3 is Yes , then I need S3= $AB$17+$AB$16+R3
If D3 is Non-Billable and Q3 is No, then S3 = 0
If D3 is Non-Billable and Q3 is Yes, then S3 = 10
<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> </tbody>

=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 came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
In the original thread I posted a much shorter formula, which I still thinks should work better for you:

DGHPQRSAAAB
1Billable / Non-BillableElectronic / MailPage Costsearch Fee Y/NPostageTotal Billed
2BillableMail100Y7.25117.25
3BillableElectronic100Y016.5
4Non - BillableMail or electronic100N0
5Non- BillableMail100Y10
6
7
8
9
10
11
12
13
14
15
16Search fee10
17Electronic6.5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
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


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

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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