2-dimensional SUMPRODUCT - Average of all applicable columns - and ignore BLANK rows/cells

[jake9951]

Hi all,

I try to create a SUMPRODUCT function that averages the seasonality for different accounts over multiple years.

Example of my source data (Input_Sheet):

Account_Number	201701	201702	... (over multiple years - in the format of "YYYYMM")
1001	120.3%	115.2%	... (in percent) - some cells/rows are empty
1002	98.2%	104.3%	... (in percent) - some cells/rows are empty
... hundreds of accounts	... (in percent) - some cells/rows are empty	... (in percent) - some cells/rows are empty	... (in percent) - some cells/rows are empty

Example of how the output should look like (Output_Sheet):

Account_Number	01	02	... (the 12 months in the format of "MM" - 01 for January, 02 for February, ...)
1001	Average Percentage for "1001" in January (over a period of multiple years)	Average Percentage for "1001" in February (over a period of multiple years)	... (in percent)
1002	Average Percentage for "1002" in January (over a period of multiple years)	Average Percentage for "1002" in February (over a period of multiple years)	... (in percent)
... hundreds of accounts (exactly the same as above)	... (in percent)	... (in percent)	... (in percent)

For now, I am only trying to make the sum that is already not working, and afterward, I would have tried to make the average out of it.

This is the formula for the first value in the output table in the upper left corner:

=SUMPRODUCT(Input_Sheet!$I$3:$AF$780 * (NUMBERVALUE(RIGHT(Input_Sheet!$I$2:$AF$2;2))=!$I$2) * (Input_Sheet!$H$3:$AF$780=$H$3))

Nevertheless, I am always getting #VALUE! errors, which is most likely because some rows/cells have in the source data only empty values.

If you also know how to create averages per month and account, I would appreciate it very much!

Thank you already in advance.

Best regards,
Jake

 

[JamesCanale]

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Account Number	201701	201702	201703	201704	201705	201706	201707	201708	201709	201710	201711	201712	201801	201802	201803	201804	201805	201806	201807	201808	201809	201810	201811	201812	201901	201902	201903
2	1001	122%	94%	129%	121%	136%	95%	51%	138%	90%	111%	124%	53%	56%	145%	112%	140%	88%	123%	79%	56%	86%	61%	110%	51%	130%	93%	84%
3	1002	134%	86%	123%	139%	77%	72%	98%	133%	118%	90%	98%	89%	58%	103%	108%	145%	147%	141%	53%	73%	147%	145%	94%	58%	56%	104%	108%
4	1003	97%	146%	122%	86%	84%	117%	145%	129%	120%	77%	87%	146%	68%	131%	70%	78%	55%	141%	53%	66%	129%	144%	128%	98%	115%	146%	119%
5	1004	81%	141%	95%	140%	95%	99%	59%	94%	54%	142%	101%	135%	104%	122%	59%	51%	78%	78%	99%	147%	78%	101%	139%	70%	119%	87%	60%
6	1005	69%	70%	119%	85%	65%	126%	77%	58%	109%	115%	140%	57%	53%	114%	62%	57%	69%	85%	107%	91%	75%	73%	96%	63%	72%	128%	80%
7
8		01	02	03	04	05	06	07	08	09	10	11	12
9	Account Number	January	February	March	April	May	June	July	August	September	October	November	December
10	1001	98%	117%	108%	122%	92%	105%	90%	107%	117%	105%	107%	79%
11	1002	86%	105%	97%	135%	103%	103%	102%	102%	134%	104%	114%	78%
12	1003	92%	132%	114%	98%	79%	116%	111%	90%	104%	111%	107%	115%
13	1004	101%	100%	69%	87%	111%	91%	84%	118%	79%	114%	109%	111%
14	1005	81%	90%	79%	88%	89%	112%	91%	89%	94%	109%	92%	63%
15
Sheet32

Cell Formulas
Range	Formula
C1:AB1	C1	=EOMONTH(B1,0)+1
B10:M14	B10	=AVERAGE(INDEX($B$2:$XFD$6,MATCH($A10,$A$2:$A$6,0),SEQUENCE(100,1,B$8,12)))