Variable array for countif/sumif?

[Myrko]

Hello,

I'm trying to make a countif / sumif formula which would look into variable row/column based on match function.


I have a table containing ID of a customer and count of drinks he/she has consumed in a period in a Sheet named AV Pivot.

	A	B	C	D	E	F	G	H	I
1	ID	Juice	Juice	Beer	Wine	Juice	Whiskey	Wine	Beer
2	12345	1	2	1	0	1	0	0	0
3	23456	0	0	1	0	0	1	0	1
4	34567	2	0	1	6	0	2	1	0

<tbody>
</tbody>

I want to create a separate sheet which will look like this:

	A	B	C
1	Customer ID	34567
2
3	Drink type	Sum
4	Juice	FORMULA HERE
5	Wine
6	Whiskey
7	Beer

<tbody>
</tbody>

I need a formula which will have the same output as if I wrote:

=SUMIF('AV Pivot'!$1:$1,"="&A4,'AV Pivot'!4:4)

but sum range should be variable based on row in which the ID (found in B1) was found in the source table (Match function can pull row number but I do not know if there is way to create variable array based on result of the match function). I know how I would fix it in VBA but I need macro free workbook.

Thanks in advance!

 

[DRSteele]

I think this might do it:

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	Juice	Juice	Beer	Wine	Juice	Whiskey	Wine	Beer
2	12345	1	2	1	0	1	0	0	0
3	23456	0	0	1	0	0	1	0	1
4	34567	2	0	1	6	0	2	1	0
5	45678	5	3	5	1	1	3	5	2
6	56789	5	5	3	3	3	5	0	2
7	67900	0	3	0	4	1	0	0	3		Customer ID	34567
8	79011	4	4	5	4	4	2	3	1
9	90122	2	5	1	3	0	4	2	0		Drink type	Sum
10	101233	4	0	3	3	1	3	2	4		Juice	2
11	112344	3	0	5	4	5	4	1	3		Wine	7
12	123455	1	0	2	2	4	2	0	1		Whiskey	2
13	134566	4	5	1	2	0	0	0	3		Beer	1
Sheet53

Cell Formulas
Range	Formula
L10	=SUMPRODUCT(--($L$7=$A$2:$A$13)*--($K10=$B$1:$I$1)*$B$2:$I$13)
L11	=SUMPRODUCT(--($L$7=$A$2:$A$13)*--($K11=$B$1:$I$1)*$B$2:$I$13)
L12	=SUMPRODUCT(--($L$7=$A$2:$A$13)*--($K12=$B$1:$I$1)*$B$2:$I$13)
L13	=SUMPRODUCT(--($L$7=$A$2:$A$13)*--($K13=$B$1:$I$1)*$B$2:$I$13)

 

[Myrko]

Thanks for the quick reply but there is one trouble: source is pivot which is variable and it can have more than this few rows/columns from the example. Is it possible to make a formula which will have just a variable rows - based on match function?