Combine SUMIFS INDEX MATCH and date range

**TRF7** · 07-12-2023, 06:45 AM

Hello Everyone,

I've been working on a file whose goal is transform quarterly revenues and costs into annual ones.
I've managed to do it thanks to INDIRECT and MATCH function but I wanted to try other (faster solutions):

1. I tried INDEX MATCH but I get a Value error (cf cell K4 of the spreadsheet)
2. I'd like to do it with SUMPRODUCT but do not where to begin with

Thanks a lot for your help !

PS: I'm not sure if I must create to separate posts since there are two questions, if so I will comply.

**AliGW** · 07-12-2023, 06:47 AM

No need for two threads - we can deal with both issues here.

Where shall I find the working formula with INDIRECT?

**TRF7** · 07-12-2023, 06:49 AM

Thanks for your reactivity!

K4 should match with E4.

**AliGW** · 07-12-2023, 07:03 AM

Try this in D4 copied across and down:

=LET(f,FILTER(Quarters!$D$4:$T$51,Quarters!$B$4:$B$51=Summary!$B4),s,FILTER(f,(Quarters!$D$2:$T$2<=Summary!D$2)*(Quarters!$D$2:$T$2>EOMONTH(EDATE(Summary!D$2,-12),0)+1)),SUM(s))

**rorya** · 07-12-2023, 07:28 AM

The equivalent INDEX/MATCH for E4 would be:

=SUMIFS(INDEX(Quarters!$E$1:$T$51,MATCH($B4,Quarters!$B$1:$B$51,0),0),Quarters!$E$2:$T$2,"<="&E$2,Quarters!$E$2:$T$2,">"&D$2)

not sure why you'd want to use SUMPRODUCT instead?

**TRF7** · 07-12-2023, 09:05 AM

Thanks both for your answer, I used Rorya's solutions since I only needed to incorporate minor changes (now I understand how the whole thing is built and that you have to respect the table dimensions in the criteria range as well).

Concerning SUMPRODUCT: this spreadsheet comes from a video on how to use the INDIRECT function, and a user commented that using SUMPRODUCT would have been faster.
(I must confess that I'm a bit fascinated by the SUMPRODUCT function and the seemingly endless possibility to tweak it that I do not master at all).

**rorya** · 07-12-2023, 09:11 AM

I don't believe SUMPRODUCT would be faster here, though there would be little difference given the size of the data ranges I suspect.

**AliGW** · 07-12-2023, 09:13 AM

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

**TRF7** · 07-12-2023, 09:16 AM

Even though it would not be necessarily faster, how would it look like ?

Thanks a lot

**rorya** · 07-12-2023, 10:16 AM

One way would be:

=SUMPRODUCT(INDEX(Quarters!$E$1:$T$51,MATCH($B4,Quarters!$B$1:$B$51,0),0)*(Quarters!$E$2:$T$2<=E$2)*(Quarters!$E$2:$T$2>D$2))

**TRF7** · 07-12-2023, 10:23 AM

Thanks for your answer, it works perfectly !

**wk9128** · 07-12-2023, 10:45 AM

Worksheet or Tab name : Summary

Cell D4 formula , Drag down and across H6

Formula:

Please Login or Register  to view this content.

**TRF7** · 07-12-2023, 11:40 AM

Thanks for the solution, a really quick one !

(Some minor changes: (Quarters!$D$2:$T$2>C$2)*(Quarters!$D$2:$T$2<=D$2) => (Quarters!$D$2:$T$2>D$2)*(Quarters!$D$2:$T$2<=E$2)

**wk9128** · 07-12-2023, 11:44 AM

@TRF7 You're Welcome. Glad to help . Thank You for the feedback and rep.

Combine SUMIFS INDEX MATCH and date range

LinkBack

Thread Tools

Rate This Thread

Display

Combine SUMIFS INDEX MATCH and date range

Re: Combine SUMIFS INDEX MATCH and date range

Re: Combine SUMIFS INDEX MATCH and date range

Re: Combine SUMIFS INDEX MATCH and date range

Re: Combine SUMIFS INDEX MATCH and date range

Re: Combine SUMIFS INDEX MATCH and date range

Re: Combine SUMIFS INDEX MATCH and date range

Re: Combine SUMIFS INDEX MATCH and date range

Re: Combine SUMIFS INDEX MATCH and date range

Re: Combine SUMIFS INDEX MATCH and date range

Re: Combine SUMIFS INDEX MATCH and date range

Re: Combine SUMIFS INDEX MATCH and date range

Re: Combine SUMIFS INDEX MATCH and date range

Re: Combine SUMIFS INDEX MATCH and date range

Thread Information

Users Browsing this Thread

Similar Threads

SUMIFS Index Match with a date range (or other Options)

[SOLVED] Index,Match,Match based on date Range - error in some cells

Index Match to find match date to a range of dates

IF statement that also uses index match to verify if date falls with date range

[SOLVED] Combine SUMIFS and INDEX MATCH - or an alternative that achieves the same...

[SOLVED] INDEX date that MATCH on date range, MATCH code(string)

[SOLVED] SUMIFS/INDEX/MATCH--looking to assign array based on Prod description & date to assign qty

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions