Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another Sumproduct & #N/A problem | Excel Worksheet Functions | |||
Sumproduct - multiple criteria in Column A | Excel Worksheet Functions | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |