Request to Simplify Sumif Formula

[velocityinc]

Please kindly help simplify the formula below such that the references
to A3, A4, A5...... could be a range (A3:A14) instead of individual
references.

={SUM(IF(('Sheet A'!$J$4:$J$37=A3)+('Sheet A'!$J$4:$J$37=A4)+('Sheet
A'!$J$4:$J$37=A5)+('Sheet A'!$J$4:$J$37=A6)+('Sheet A'!$J$4:$J$37=A7)+
('Sheet A'!$J$4:$J$37=A8)+('Sheet A'!$J$4:$J$37=A9)+('Sheet A'!$J$4:$J
$37=A10)+('Sheet A'!$J$4:$J$37=A11)+('Sheet A'!$J$4:$J$37=A12)+('Sheet
A'!$J$4:$J$37=A13)+('Sheet A'!$J$4:$J$37=A14),1,0))}

Many thanks,
V

 

[Don Guillett]

try this idea
=SUMPRODUCT((D222>E2)*(D222<E14)*D222)

 

[velocityinc]

try this idea
=SUMPRODUCT((D222>E2)*(D222<E14)*D222)

--
Don Guillett
SalesAid Software







- Show quoted text -

Unfortunately, Im not sure how to apply this ...would it be
=SUMPRODUCT((A3:A14>Perf!$J$4:$J$37)*A3:A14), but this shows up with
the #value!.....please assist.

 

[Bob Phillips]

=SUMPRODUCT(--ISNUMBER(MATCH(A3:A14,Perf!$J$4:$J$37,0)))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)