# INDEX Partial MATCH and Total

1. ## INDEX Partial MATCH and Total

Afternoon guys

I am trying to partial match using INDEX MATCH and think I am almost there

In the example (see attachment) I want to find all the "Food Shop" in November so I was trying to partial match using "*nov*" and then I need all the matches added together

Can anyone see where I have gone wrong?

Thanks!

Jonny

2. ## Re: INDEX Partial MATCH and Total

Try

=SUMPRODUCT(--(D1:D11=I11),--(TEXT(B1:B11,"mmm")=I10),F1:F11)

Change I11 to just Nov (no *'s)

Will you also need to account for the year?

3. ## Re: INDEX Partial MATCH and Total

Formula:
`Please Login or Register  to view this content.`

4. ## Re: INDEX Partial MATCH and Total

Do you mean like a year to date? That would be amazing if possible!

5. ## Re: INDEX Partial MATCH and Total

No, what I meant was in column B, if you have dates from multiple years.
Say you have 11/15/2014 and 11/15/2013
Should they both be counted, or only the 2014 ?

6. ## Re: INDEX Partial MATCH and Total

Ohh sorry yeah they should definitely be counted

7. ## Re: INDEX Partial MATCH and Total

Originally Posted by flapface
they should definitely be counted
I assume you mean they should both becounted...

Make sure the date range you're using doesn't contain blanks.
They would be counated as January.

This shouldn't be a problem, since you have a 2nd criteria "Food Shop"
Presumably if the date is blank, then the column D would be blank as well..

8. ## Re: INDEX Partial MATCH and Total

Morning guys

The sumproduct formula really slows down my spreadsheet is there another way round this? I have seen similar problems use INDEX MATCH however I am not that clued up on the function I must be close with this formula

=INDEX(F:F,MATCH("Nov",IF(C:C=I11,B:B),0))??

Thanks guys!

Jonny

9. ## Re: INDEX Partial MATCH and Total

The sumproduct formula really slows down my spreadsheet is there another way round this?
Don't use whole columns reference in SUMPRODUCT.

Alternative you can use a helper(and hidden if you like) column with this formula

Formula:
`Please Login or Register  to view this content.`

and then a SUMIF to get your result.
Formula:
`Please Login or Register  to view this content.`

10. ## Re: INDEX Partial MATCH and Total

Originally Posted by flapface
The sumproduct formula really slows down my spreadsheet
Originally Posted by Fotis1991
Don't use whole columns reference in SUMPRODUCT.
+1

11. ## Re: INDEX Partial MATCH and Total

That is soooo much better! I used the full columns as this is a data sheet that will be entered for many years I set it to the first 1000 entries is there a way the formula can take only the rows of the table perhaps a dynamic range?

12. ## Re: INDEX Partial MATCH and Total

I believe is better to use whole columns reference in SUMIF, rather than to create dynamic ranges. There is no reason for that.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1