My formula is too long, What am I to do?

[Guest]

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$1500>0),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98110),--(DATA!$AB$2:$AB$1500>0),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98311),--(DATA!$AB$2:$AB$1500>0),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98315),--(DATA!$AB$2:$AB$1500>0),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98340),--(DATA!$AB$2:$AB$1500>0),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98342),--(DATA!$AB$2:$AB$1500>0),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98345),--(DATA!$AB$2:$AB$1500>0),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98346),--(DATA!$AB$2:$AB$1500>0),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98364),--(DATA!$AB$2:$AB$1500>0),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98370),--(DATA!$AB$2:$AB$1500>0),--(DATA!$AB$2:$AB$1500<=5))+SUMPRODUCT(--(DATA!$C$2:$C$1500=98392),--(DATA!$AB$2:$AB$1500>0),--(DATA!$AB$2:$AB$1500<=5))

 

[Guest]

Worked great! Should have learned that one along time ago. Great help!
--
Scott Miller
University of Washington
Chemistry

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:AB1500>0),--(AB2:AB1500<=5))

Does that help?

***********
Regards,
Ron

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

 

[Guest]

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:AB1500>0),--(AB2:AB1500<=5))

Does that help?

***********
Regards,
Ron

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