formula too long

[orrc]

How can I shorten up this formula as I need to add a few more variables
to it. When I try and add another variable Excel says the formula is
too long.
Thanks

=IF(AND(A16="DeKalb",D16="Poncho
250"),0)+IF(AND(A16="DeKalb",D16="Cruiser Ext.
Pak"),0)+IF(AND(A16="Dekalb",D16="Cruiser
CRW"),48)+IF(AND(A16="DeKalb",D16="Poncho
1250"),48)+IF(AND(A16="Asgrow",D16="Poncho
250"),0)+IF(AND(A16="Asgrow",D16="Cruiser Ext.
Pak"),0)+IF(AND(A16="Asgrow",D16="Cruiser
CRW"),48)+IF(AND(A16="Asgrow",D16="Poncho
1250"),48)+IF(AND(A16="Croplan",D16="Poncho
250"),0)+IF(AND(A16="Croplan",D16="Cruiser Ext.
Pak"),0)+IF(AND(A16="Croplan",D16="Cruiser
CRW"),48)+IF(AND(A16="Croplan",D16="Poncho
1250"),48)+IF(AND(A16="Northrup King",D16="Poncho
250"),16)+IF(AND(A16="Northrup King",D16="Cruiser Ext.
Pak"),16)+IF(AND(A16="Northrup King",D16="Cruiser
CRW"),48)+IF(AND(A16="Northrup King",D16="Poncho
1250"),48)+IF(AND(A16="Mycogen",D16="Poncho
250"),16)+IF(AND(A16="Mycogen",D16="Cruiser Ext.
Pak"),16)+IF(AND(A16="Mycogen",D16="Cruiser
CRW"),48)+IF(AND(A16="Mycogen",D16="Poncho 1250"),48)

 

[Anne Troy]

You can only have IF in a formula a total of 8 times (7 nested IFs).
************
Anne Troy
www.OfficeArticles.com

 

[Ken Wright]

Create a table in say F1:H20 and put all your data in it

DeKalb Poncho 250 0
DeKalb Cruiser Ext.Pak 0
Dekalb Cruiser CRW 48
DeKalb Poncho 1250 48
Asgrow Poncho 250 0
Asgrow Cruiser Ext. Pak 0
Asgrow Cruiser CRW 48
Asgrow Poncho 1250 48
Croplan Poncho 250 0
Croplan Cruiser Ext.Pak 0
Croplan Cruiser CRW 48
Croplan Poncho 1250 48
Northrup King Poncho 250 16
Northrup King Cruiser Ext. Pak 16
Northrup King Cruiser CRW 48
Northrup King Poncho 1250 48
Mycogen Poncho 250 16
Mycogen Cruiser Ext. Pak 16
Mycogen Cruiser CRW 48
Mycogen Poncho 1250 48


Then use a formula such as the following:-

=SUMPRODUCT(--(F1:F20=A16),--(G1:G20=D16),(H1:H20))

Make it almost as big as you like and just adjust the ranges. Much much
easier to administer as well.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Sandy Mann]

You can only have IF in a formula a total of 8 times (7 nested IFs).

Anne,

I don't think that this applies in this case. orrc doesn't have a FALSE
argument in any of his/her IF's but they are not nested they are a serries
of IF's added together. I believe that the reason was because there was
more than 1016 characters in the cell constituting the formula.

--
Regards

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Anne Troy]

LOL, Sandy. I didn't even look that far. Thanks. I also didn't have a
clue where to begin for a solution either.
************
Anne Troy
www.OfficeArticles.com