Distinct count of one column based on another column

[chad.portman]

Ok so this is kinda hard to explain but let me try. I have a set of records over 1million long. This is a log sheet of sales. So Col A is Cust ID and Col B is sales date. I need to be able to find out how many months throughout the year each cust ID bought something. So for a small example I have included an attachment that also shows my desired results. Sample data.xlsx

I basically need to find a way to get sumif of the distinct months but I am having a brain fart on how to do that.

[ajryan88]

Hi,

If you insert a helper column (use column C), and insert the following formula in C2:

Please Login or Register  to view this content.

Then fill the formula down to the end of your data. Then use this array formula to count the months:

Please Login or Register  to view this content.

A working example of this formula can be seen in the attachment on the AJRYAN88 worksheet.

NB: This is an array formula, and needs to be entered using Ctrl + Shift + Enter, rather than just Enter.

I hope this helps

[chad.portman]

Thanks for the help this appears to work. next question since i am new to Array Formulas can I just copy the formula down the Col like I do any other formula or is there something special for that part as well.

[ajryan88]

It depends on how the formula is written. However, I wrote this one so that it can indeed be filled down as far as is required

Please don't forget to mark this thread as solved (Thread Tools --> Mark As Solved), and please click on the * next to my post to say thanks