Summary Table: Index Match?

[cdubs34]

Hello,

I have a large set of data with the following being a small sample of what it looks like:

Name	Product	Price
John Smith	Hat	$10
Jane Doe	Coat	$20
Tom Jones	Pants	$15
Steve Smith	Coat	$18
Jane Doe	Pants	$20
Tom Jones	Pants	$25

<tbody>
</tbody>

I then ultimately need to create a summary chart using formulae that will look something like this:

	# Items	# Items	# Items	Spend	Spend	Spend
	Hats	Coats	Pants	Hats	Coats	Pants
John Smith
Jane Does
Tom Jones
Steve Smith

<tbody>
</tbody>

Could you please help me out with a formula that would fill out for each person how many of each item they bought and then how much they spent in total on each item? Not sure if this would be an Index Match formula or something different. Your help is much appreciated! Thanks.

C

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Name	Product	Price			# Items	# Items	# Items	Spend	Spend	Spend
2​	John Smith	Hats	$10 ​			Hats	Coats	Pants	Hats	Coats	Pants
3​	Jane Doe	Coat	$20 ​		John Smith	1​	0​	0​	10​	0​	0​
4​	Tom Jones	Pants	$15 ​		Jane Doe	0​	0​	1​	0​	0​	20​
5​	Steve Smith	Coat	$18 ​		Tom Jones	0​	0​	2​	0​	0​	40​
6​	Jane Doe	Pants	$20 ​		Steve Smith	0​	0​	0​	0​	0​	0​
7​	Tom Jones	Pants	$25 ​

In F3 enter, copy across, and down:

=IF(F$1="# Items",COUNTIFS($A:$A,$E3,$B:$B,F$2),SUMIFS($C:$C,$A:$A,$E3,$B:$B,F$2))

 

[cdubs34]

Ahhh perfect, that is it!! Thank you so much!!

 

[Aladin Akyurek]

Ahhh perfect, that is it!! Thank you so much!!

You are welcome.