Index & Match

D

Dominique Feteau

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
 
F

Frank Kabel

Hi in this case use SUMPRODUCT. Try:
=SUMPRODUCT(--(A2:A34=C37),--(B2:B34=D27),OFFSET(A2:A34,0,MATCH(C38,Months,0)+1))
 
D

Dominique Feteau

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
 
F

Frank Kabel

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
 
D

Dominique Feteau

frank...

works like a charm

thanks

Frank Kabel said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top