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

S

Scott

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))
 
S

Scott

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


Ron Coderre said:
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


Scott said:
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
Click to expand...
Click to expand...
 
R

Ron Coderre

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


Scott said:
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
Click to expand...
 
Top