|
My formula is too long, What am I to do?
My formula given below needs to be modified and excel will not allow me to
add digits. Is there another way to write this formula that would be more concise or can I extent the capacity of certain cells? Scott Miller University of Washington =SUMPRODUCT(--(DATA!$C$2:$C$1500=98061),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98110),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98311),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98315),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98340),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98342),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98345),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98346),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98364),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98370),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98392),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5)) -- Scott Miller University of Washington Chemistry |
My formula is too long, What am I to do?
Worked great! Should have learned that one along time ago. Great help!
-- Scott Miller University of Washington Chemistry "Ron Coderre" wrote: Try this: First, put your list of 1st match values in a vertical list. I'll assume they're in A1:A11.... A1: 98061 A2: 98110 etc (make sure they're in ascending order for easier checking) Then, use this formula: =SUMPRODUCT(--ISNUMBER(MATCH(C2:C1500,A1:A11,0)),--(AB2:AB15000),--(AB2:AB1500<=5)) Does that help? *********** Regards, Ron "Scott" wrote: My formula given below needs to be modified and excel will not allow me to add digits. Is there another way to write this formula that would be more concise or can I extent the capacity of certain cells? Scott Miller University of Washington =SUMPRODUCT(--(DATA!$C$2:$C$1500=98061),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98110),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98311),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98315),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98340),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98342),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98345),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98346),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98364),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98370),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98392),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5)) -- Scott Miller University of Washington Chemistry |
My formula is too long, What am I to do?
Try this:
First, put your list of 1st match values in a vertical list. I'll assume they're in A1:A11.... A1: 98061 A2: 98110 etc (make sure they're in ascending order for easier checking) Then, use this formula: =SUMPRODUCT(--ISNUMBER(MATCH(C2:C1500,A1:A11,0)),--(AB2:AB15000),--(AB2:AB1500<=5)) Does that help? *********** Regards, Ron "Scott" wrote: My formula given below needs to be modified and excel will not allow me to add digits. Is there another way to write this formula that would be more concise or can I extent the capacity of certain cells? Scott Miller University of Washington =SUMPRODUCT(--(DATA!$C$2:$C$1500=98061),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98110),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98311),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98315),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98340),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98342),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98345),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98346),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98364),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98370),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98392),--(DATA!$AB$2:$AB$15000),--(DATA!$AB$2:$AB$1500<=5)) -- Scott Miller University of Washington Chemistry |
| All times are GMT +1. The time now is 08:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com