If Statement formula issue without picture issue lol

Szucchari

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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,425
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>
 

Szucchari

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

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,425
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?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,425
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.
 

Forum statistics

Threads
1,172,203
Messages
5,879,663
Members
433,450
Latest member
angelam01

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
Top