How can I shorten this formula, I still need to add a few more variables
and everytime I try and add one I get the formula is too long error.

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)


--
cencoit
------------------------------------------------------------------------
cencoit's Profile: http://www.excelforum.com/member.php...o&userid=27378
View this thread: http://www.excelforum.com/showthread...hreadid=468987

See attachment, hope it helps.


cencoit Wrote:
How can I shorten this formula, I still need to add a few more variables
and everytime I try and add one I get the formula is too long error.

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)


+-------------------------------------------------------------------+
|Filename: Match.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3839 |
+-------------------------------------------------------------------+

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=468987

The error message is Excel's way of trying to tell you to give up the formula
and use VLOOKUP() instead.


Create a table with the text combinations going down column X and the values
going down column Z:

DeKalbPoncho250 0
....

The formula could then be =VLOOKUP(A16&D16,X1:Z20,2,0)

The advantage of this approach is that to make it 120 combinations instead
of 20 only requires making the table bigger and changing the formula to

=VLOOKUP(A16&D16,X1:Z120,2,0)

--
Gary''s Student


"cencoit" wrote:


How can I shorten this formula, I still need to add a few more variables
and everytime I try and add one I get the formula is too long error.

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)


--
cencoit
------------------------------------------------------------------------
cencoit's Profile: http://www.excelforum.com/member.php...o&userid=27378
View this thread: http://www.excelforum.com/showthread...hreadid=468987

:) Thanks for the help, it work very well


--
cencoit
------------------------------------------------------------------------
cencoit's Profile: http://www.excelforum.com/member.php...o&userid=27378
View this thread: http://www.excelforum.com/showthread...hreadid=468987