# If Statement formula issue without picture issue lol

#### Szucchari

##### New Member
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.

 D G H P Q R S AA AB Billable / Non - Billable Shipment Type Electronic / mail Page cost Search Fee Yes /No Postage Total 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
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

</tbody>
Sheet4

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

</tbody>

<tbody>
</tbody>

Thanks Eric

You're welcome.

#### Szucchari

##### New Member
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
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

##### New Member
I am so sorry, I ment if Q3 is Y,

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

#### Eric W

##### MrExcel MVP
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.

Replies
4
Views
288
Replies
4
Views
122
Replies
1
Views
546
Replies
1
Views
3K
Replies
2
Views
513

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.

### Which adblocker are you using?

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

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