Massive Nested IF Statement Help Needed

[Zutalores]

Hello EXCEL geniuses. I have a doozy Nested IF statement that I cannot figure out for the life of me. At the bottom is a short dataset to represent what I am working with. What I am trying to work out is one Nested IF statement to achieve a total freight amount based on the parameters in a Freight Policy. I was only able to do it by separating my parameters and then summing them in a new cell. But I would like a statement that would achieve all of the parameters in one go.

Current Formulas:
Column Q =IF(AND(OR(C2="AK",C2="HI"),H2<>"Nutritionals & Feeding"),IF(O2=1,20,10)*(IF(ISERROR(N2),0,N2)),0)

Column R =IF(AND(OR(C2="AK",C2="HI"),H2="Nutritionals & Feeding"), 34.95, IF(H2="Nutritionals & Feeding", 24.95, IF(AND(I2="Yes", OR(C2="AK",C2="HI")), IF(COUNTIF($A$2:A2,A2)=1, 24.95, 0), 0)))

Column S =IF(I2="Yes", IF(COUNTIF($A$2:A2,A2)=1,14.95,0), 0)

Column T =IF(IF(COUNTIF(H:H, "Incontinence")>0, IF(COUNTIF($A$2:A2, A2)=1, IF(SUMIFS(O:O, A:A, A2, H:H, "Incontinence")=1, 6, 6 + (SUMIFS(O:O, A:A, A2, H:H, "Incontinence")-1)*4), 0), 0) = 2, 0, IF(COUNTIF(H:H, "Incontinence")>0, IF(COUNTIF($A$2:A2, A2)=1, IF(SUMIFS(O:O, A:A, A2, H:H, "Incontinence")=1, 6, 6 + (SUMIFS(O:O, A:A, A2, H:H, "Incontinence")-1)*4), 0), 0))

Column U =IF(AND(NOT(OR(C2="AK",C2="HI")), NOT(OR(H2="Nutritionals & Feeding", H2="Incontinence")), COUNTIFS($A$2:A2, A2, $H$2:H2, "<>Nutritionals & Feeding", $H$2:H2, "<>Incontinence")=1), 6, 0)

Column V =SUM(Q2:U2)

This is all based on a crazy Freight Policy and our current accounting software cannot make the calculations needed to get a total freight amount per order. The freight policy is below.

"Incontinence" (column D) items are $6.00 for the first case and $4.00 for each additional case per order within the contiguous US.
"Nutritionals & Feedings" (column D) items are $24.95 per order in contiguous US and $34.95 for AK, HI, and PR.
"Lightweight DME" (if column E = "Yes") orders are $14.95 per order to contiguous US and $24.95 to AK, HI, and PR.
All other items, not listed above are $6.00 per order to contiguous US.
All other items to AK, HI, and PR are $20.00 for the first case, and $10.00 for each additional case per order.

Any help would be greatly appreciated!!!

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

SalesOrderNumber

PO Number

Ship to State

MDCategoryName

Light DME

Qty

Each Qty

Parent item

U

M

Cases Shipped rounded up

C Freight

hi or ak not nutritional

nutritional

light dme

inco

non inco

sum of freight

2

614413890​

5690892​

KY

Wound Care

12​

12​

ALA595300

50​

CS

1​

0​

0​

0​

0​

0​

6​

6​

3

614413890​

5690892​

KY

Wound Care

30​

30​

MSC8722EP

50​

CS

1​

0​

0​

0​

0​

0​

0​

0​

4

614427958​

5691117​

TN

Incontinence

14​

280​

FITULTRAMD

80​

CS

4​

0​

0​

0​

0​

42​

0​

42​

5

614427958​

5691117​

TN

Gloves

1​

100​

MDS195176

1000​

CS

1​

0​

0​

0​

0​

0​

6​

6​

6

614427958​

5691117​

TN

Incontinence

26​

26​

MSC263854

6​

CS

5​

0​

0​

0​

0​

0​

0​

0​

7

614427958​

5691117​

TN

Incontinence

4​

64​

MSC53005

64​

CS

1​

0​

0​

0​

0​

0​

0​

0​

 

[Zutalores]

Apologies, I had the wrong column notes for the existing formulas, please see below.

Column M =IF(AND(OR(C2="AK",C2="HI"),H2<>"Nutritionals & Feeding"),IF(O2=1,20,10)*(IF(ISERROR(N2),0,N2)),0)

Column N =IF(AND(OR(C2="AK",C2="HI"),H2="Nutritionals & Feeding"), 34.95, IF(H2="Nutritionals & Feeding", 24.95, IF(AND(I2="Yes", OR(C2="AK",C2="HI")), IF(COUNTIF($A$2:A2,A2)=1, 24.95, 0), 0)))

Column O =IF(I2="Yes", IF(COUNTIF($A$2:A2,A2)=1,14.95,0), 0)

Column P =IF(IF(COUNTIF(H:H, "Incontinence")>0, IF(COUNTIF($A$2:A2, A2)=1, IF(SUMIFS(O:O, A:A, A2, H:H, "Incontinence")=1, 6, 6 + (SUMIFS(O:O, A:A, A2, H:H, "Incontinence")-1)*4), 0), 0) = 2, 0, IF(COUNTIF(H:H, "Incontinence")>0, IF(COUNTIF($A$2:A2, A2)=1, IF(SUMIFS(O:O, A:A, A2, H:H, "Incontinence")=1, 6, 6 + (SUMIFS(O:O, A:A, A2, H:H, "Incontinence")-1)*4), 0), 0))

Column Q =IF(AND(NOT(OR(C2="AK",C2="HI")), NOT(OR(H2="Nutritionals & Feeding", H2="Incontinence")), COUNTIFS($A$2:A2, A2, $H$2:H2, "<>Nutritionals & Feeding", $H$2:H2, "<>Incontinence")=1), 6, 0)

Column R =SUM(Q2:U2)