YTD INDEX MATCH

[reacon84]

Hi all,

I've searched the forum but can't quite see what I'm after.

I have a budget file as below, and I would like the outputs in green to sum the YTD numbers. If Mar-22 is selected in A1, then I'd like columns C to E to be summed for each category. And if I change the date in A1, it will change the YTD numbers. I think it will be some sort of INDEX MATCH combo but can't quite get it to work!

Thanks in advance.

Mar-22
		Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22
	Revenue	30	30	30	30	30	30	30	30	30	30	30	30
	COS	10	10	10	10	10	10	10	10	10	10	10	10
	Costs	15	15	15	15	15	15	15	15	15	15	15	15
Revenue	90
COS	30
Costs	45

 

[jasonb75]

No index or match needed, just enter this formula into B8 then fill down,

=SUMPRODUCT(($C$3:$N$5)*($C$2:$N$2<=$A$1)*($B$3:$B$5=$A8))

 

[reacon84]

No index or match needed, just enter this formula into B8 then fill down,

=SUMPRODUCT(($C$3:$N$5)*($C$2:$N$2<=$A$1)*($B$3:$B$5=$A8))

Thank you!

I've heard that sumproduct formulas can really slow down a file - is this true?

The example I used was just an extract of a really large file, so I'm just conscious that the speed of the file will be affected?

 

[jasonb75]

Sumproduct is an array function, so yes it can slow it down with large amounts of data. That said, it depends on what you consider large. If you have 3 formulas looking at #100k rows then it's not going to be noticeable. A lot of formulas looking at a small amount of data can be slower than a few formulas looking at a lot of data.

Are there multiple rows that will meet a single criteria in the actual data?

 

[reacon84]

Sumproduct is an array function, so yes it can slow it down with large amounts of data. That said, it depends on what you consider large. If you have 3 formulas looking at #100k rows then it's not going to be noticeable. A lot of formulas looking at a small amount of data can be slower than a few formulas looking at a lot of data.

Are there multiple rows that will meet a single criteria in the actual data?

Thanks for the explanation, makes sense. There are only about 200 rows (ie 200 formulas), so probably won't a difference based on what you say?

"Are there multiple rows that will meet a single criteria in the actual data?" As in my example, like 3 x "Revenue" in col B? No, they are all unique values (names of suppliers).

 

[jasonb75]

200 formulas isn't bad, but it also depends how many rows of source data you have (C3:N5 in the example).

A different method that may be a bit faster depending on a few other things (order and number of rows in the source).

=SUMIFS(INDEX($C$3:$N$5,MATCH($A8,$B$3:$B$5,LOOKUP($A8,$B$3:$B$5)=$A8),0),$C$2:$N$2,"<="&$A$1)

The formula looks more complicated because it uses 2 match methods in 1 in an attempt to speed things up. If the first one finds a correct match then it will be very fast, if it doesn't then the second one will be slower but should still be a bit faster than sumproduct.