Sumproduct questin
 

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

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)



"Lee" wrote in message
...
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

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

--
Don Guillett
SalesAid Software

"Lee" wrote in message
...
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

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

Lee wrote:
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