|
Index & Match
I got a hand from someone on here (frank) with this function:
=INDEX(A2:O34,MATCH(1,(C37=A2:A34)*(D37=B2:B34),0) ,MATCH(C38,Months,0)+2) I do have a questioni about it tho. How could I tweak it so that it if there are 2 or more columns with the same value that it will add them together? thanks niq |
Hi in this case use SUMPRODUCT. Try:
=SUMPRODUCT(--(A2:A34=C37),--(B2:B34=D27),OFFSET(A2:A34,0,MATCH(C38,Months,0)+1 )) -- Regards Frank Kabel Frankfurt, Germany Dominique Feteau wrote: I got a hand from someone on here (frank) with this function: =INDEX(A2:O34,MATCH(1,(C37=A2:A34)*(D37=B2:B34),0) ,MATCH(C38,Months,0)+2) I do have a questioni about it tho. How could I tweak it so that it if there are 2 or more columns with the same value that it will add them together? thanks niq |
I made some changes to the code to adjust to my new sheet and it works, but
it doesnt sum up the 2 columns. =SUMPRODUCT(--(A4:A35=A4),--(B4:B35=B4),OFFSET(A4:A35,0,MATCH(A2,D2:K2,0)+2) ) A4:A35 = Form C4:C35 = Form Explanation A2 = County to look up D2:K2 = Counties So I want it to look in A2 and match it to those in D2:K2 (and sum if there are 2 or more) and since there some of C4:C35 that are the same, make sure it matches the row that has both whats in A4:A35. Hope thats not too confusing... Thanx "Frank Kabel" wrote in message ... Hi in this case use SUMPRODUCT. Try: =SUMPRODUCT(--(A2:A34=C37),--(B2:B34=D27),OFFSET(A2:A34,0,MATCH(C38,Months,0 )+1)) -- Regards Frank Kabel Frankfurt, Germany Dominique Feteau wrote: I got a hand from someone on here (frank) with this function: =INDEX(A2:O34,MATCH(1,(C37=A2:A34)*(D37=B2:B34),0) ,MATCH(C38,Months,0)+2) I do have a questioni about it tho. How could I tweak it so that it if there are 2 or more columns with the same value that it will add them together? thanks niq |
Hi
this formula would sum only the first occurence of your value in A2 in the range D2:K2. Not tested but try: =SUMPRODUCT((A4:A35=A4)*(B4:B35=B4)*(D2:K2=D2)*(D4 :K35)) But as said: I didn't test this -- Regards Frank Kabel Frankfurt, Germany Dominique Feteau wrote: I made some changes to the code to adjust to my new sheet and it works, but it doesnt sum up the 2 columns. =SUMPRODUCT(--(A4:A35=A4),--(B4:B35=B4),OFFSET(A4:A35,0,MATCH(A2,D2:K2,0)+2) ) A4:A35 = Form C4:C35 = Form Explanation A2 = County to look up D2:K2 = Counties So I want it to look in A2 and match it to those in D2:K2 (and sum if there are 2 or more) and since there some of C4:C35 that are the same, make sure it matches the row that has both whats in A4:A35. Hope thats not too confusing... Thanx "Frank Kabel" wrote in message ... Hi in this case use SUMPRODUCT. Try: =SUMPRODUCT(--(A2:A34=C37),--(B2:B34=D27),OFFSET(A2:A34,0,MATCH(C38,Months,0 )+1)) -- Regards Frank Kabel Frankfurt, Germany Dominique Feteau wrote: I got a hand from someone on here (frank) with this function: =INDEX(A2:O34,MATCH(1,(C37=A2:A34)*(D37=B2:B34),0) ,MATCH(C38,Months,0)+2) I do have a questioni about it tho. How could I tweak it so that it if there are 2 or more columns with the same value that it will add them together? thanks niq |
frank...
works like a charm thanks "Frank Kabel" wrote in message ... Hi this formula would sum only the first occurence of your value in A2 in the range D2:K2. Not tested but try: =SUMPRODUCT((A4:A35=A4)*(B4:B35=B4)*(D2:K2=D2)*(D4 :K35)) But as said: I didn't test this -- Regards Frank Kabel Frankfurt, Germany Dominique Feteau wrote: I made some changes to the code to adjust to my new sheet and it works, but it doesnt sum up the 2 columns. =SUMPRODUCT(--(A4:A35=A4),--(B4:B35=B4),OFFSET(A4:A35,0,MATCH(A2,D2:K2,0)+2) ) A4:A35 = Form C4:C35 = Form Explanation A2 = County to look up D2:K2 = Counties So I want it to look in A2 and match it to those in D2:K2 (and sum if there are 2 or more) and since there some of C4:C35 that are the same, make sure it matches the row that has both whats in A4:A35. Hope thats not too confusing... Thanx "Frank Kabel" wrote in message ... Hi in this case use SUMPRODUCT. Try: =SUMPRODUCT(--(A2:A34=C37),--(B2:B34=D27),OFFSET(A2:A34,0,MATCH(C38,Months,0 )+1)) -- Regards Frank Kabel Frankfurt, Germany Dominique Feteau wrote: I got a hand from someone on here (frank) with this function: =INDEX(A2:O34,MATCH(1,(C37=A2:A34)*(D37=B2:B34),0) ,MATCH(C38,Months,0)+2) I do have a questioni about it tho. How could I tweak it so that it if there are 2 or more columns with the same value that it will add them together? thanks niq |
Hi
thanks for the feedback :-) -- Regards Frank Kabel Frankfurt, Germany Dominique Feteau wrote: frank... works like a charm thanks "Frank Kabel" wrote in message ... Hi this formula would sum only the first occurence of your value in A2 in the range D2:K2. Not tested but try: =SUMPRODUCT((A4:A35=A4)*(B4:B35=B4)*(D2:K2=D2)*(D4 :K35)) But as said: I didn't test this -- Regards Frank Kabel Frankfurt, Germany Dominique Feteau wrote: I made some changes to the code to adjust to my new sheet and it works, but it doesnt sum up the 2 columns. =SUMPRODUCT(--(A4:A35=A4),--(B4:B35=B4),OFFSET(A4:A35,0,MATCH(A2,D2:K2,0)+2) ) A4:A35 = Form C4:C35 = Form Explanation A2 = County to look up D2:K2 = Counties So I want it to look in A2 and match it to those in D2:K2 (and sum if there are 2 or more) and since there some of C4:C35 that are the same, make sure it matches the row that has both whats in A4:A35. Hope thats not too confusing... Thanx "Frank Kabel" wrote in message ... Hi in this case use SUMPRODUCT. Try: =SUMPRODUCT(--(A2:A34=C37),--(B2:B34=D27),OFFSET(A2:A34,0,MATCH(C38,Months,0 )+1)) -- Regards Frank Kabel Frankfurt, Germany Dominique Feteau wrote: I got a hand from someone on here (frank) with this function: =INDEX(A2:O34,MATCH(1,(C37=A2:A34)*(D37=B2:B34),0) ,MATCH(C38,Months,0)+2) I do have a questioni about it tho. How could I tweak it so that it if there are 2 or more columns with the same value that it will add them together? thanks niq |
| All times are GMT +1. The time now is 10:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com