matrix of COUNT IF

apgmin

Board Regular
Joined
Mar 1, 2010
Messages
119
Office Version
  1. 2013
Platform
  1. Windows
Cell A4:A5000 has the receipt dates
In correspondence cell B4:B5000 I use the product sold vide that receipt ( Item 1 , Item 2 so on )

Now I need to populate the matrix

Cell AL4 to AL15 has the months listed ( Apr, May, Jun, Jul . . . Oct, Nov, Dec )

Cell AM3 is mentioned as Item 1, Cell AN 3 has the words Item 2 etc till Item 4

I need help so that I know how many item 1 was sold in April in cell AM4, followed by how many item 2 was sold in cell AN4

The formula should check the sale date in A4:A5000 and also check the item name in cell B4:B5000

Cell AL3, MONTHCell AM3, ITEM 1Cell AN3,ITEM 2ITEM 3ITEM 4
Cell AL4, APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
JAN
FEB
MAR
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,138
Office Version
  1. 365
Platform
  1. Windows
Why not just use a simple pivot table? Use months (from the dates) as pivot rows and item names as pivot columns.
No mention of where to find quantity in your post, if that is in a third column then you can use sum of quantity for pivot values, or if you just want to count how many entries are listed for each item then you can use count of items instead.

Far easier and more efficient than formulas.
 

Forum statistics

Threads
1,136,274
Messages
5,674,777
Members
419,524
Latest member
helensesc

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?

Disable AdBlock

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
Top