Sumproduct questin

L

Lee

I have the following sumproduct formula

=SUMPRODUCT((Opp_Detail_Date>=B5)*(Opp_Detail_Date<B6)*
((Opp_Detail_Name=Index!B2)+(Opp_Detail_Name=Index!B3)))

Opp_Detail_Date and Opp_Detail_Name are named ranges.

I want to extend this part of the
formula "((Opp_Detail_Name=Index!B2)+
(Opp_Detail_Name=Index!B3))" so that is calculates the
results for the range Index!B2:B73.

There has to be a better way of doing this then adding "+
(Opp_Detail_Name=Index!B14)" etc 71 times.

Any suggestions?

Thx
Lee
 
P

Peo Sjoblom

Try something like

=SUM((Opp_Detail_Date>=B5)*(Opp_Detail_Date<B6)*((Opp_Detail_Name=TRANSPOSE(Index!B2:B73))))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
D

Don Guillett

Can't you just change the 1st one and copy down or use a macro
for each c
c.value=c&ete
next
 
A

Aladin Akyurek

=SUMPRODUCT(--(Opp_Detail_Date>=B5),--(Opp_Detail_Date<B6),--ISNUMBER(MATCH(Opp_Detail_Name,Index!B2:B73,0)))
 
Top