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