Gareth has attended:
Excel VBA Intro Intermediate course
Excel Intermediate course
Excel Advanced course
Sumproduct - counting distinct values in two columns
Hi
I have this formula to count the number of distinct entries in one column.
=SUMPRODUCT(($H$23:$H$65536<>"")/COUNTIF($H$23:$H$65536,$H$23:$H$65536&""))
I need to adapt this formula to count the distinct entries that match a value in column S as well. This Column contains the month.
So essentially I need to count the distinct entries that match a particular month.
Any suggestions? Thanks
gareth
RE: Sumproduct - counting distinct values in two columns
Hi Gareth
Thank you for your enquiry. You may be able to extend your SUMPRODUCT statement to include selection criteria. Whilst it's not formerly covered in our training courses you may find section 3 of the following article of help.
http://www.meadinkent.co.uk/xlsumproduct.htm
I hope this helps - do let us know if you have any further questions.
Kind regards,
Andrew
RE: Sumproduct - counting distinct values in two columns
Hi Andrew
Thanks for the reply. I wouldn't know where to beging amending my original formula!
Any advice on how I go about extending it to include another criteria from another column?
Thanks
RE: Sumproduct - counting distinct values in two columns
Hi Gareth
Thank you for coming back to me. I have asked a colleague to look at your question as this is an area he as worked on. Please bear in mind he will be back on Wednesday. Please let me know if this will be a problem.
Kind regards,
Andrew
RE: Sumproduct - counting distinct values in two columns
Hi Gareth
Thank you for your question which Andrew has passed to me.
First I apologise for the delay in responding, this was due to an oversight on my part.
If this is still an outstanding problem I suggest yo email me an example of the workbook with information as to what exactly you wish to achieve and I will give the matter my urgent attention
My email is sw@stl-training.co.uk
Regards
Stephen
