# Summary Table: Index Match?

#### cdubs34

##### New Member
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

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

##### MrExcel MVP
 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

##### New Member
Ahhh perfect, that is it!! Thank you so much!!

Replies
5
Views
225
Replies
0
Views
587
Replies
5
Views
488
Replies
9
Views
404
Replies
1
Views
965

1,191,578
Messages
5,987,413
Members
440,096
Latest member
yanaungmyint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back