Forum home » Delegate support and help forum » Microsoft Excel Training and help » Sumproduct - counting distinct values in two columns
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Sumproduct - counting distinct values in two columns
Resolved · High Priority · Version 2003
Reply Mon 2 Nov 2009, 10:48Delegate Gareth said...
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
For upcoming training course dates see: Pricing & availability
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
Reply Mon 2 Nov 2009, 11:41Delegate Gareth said...
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
Regards
Stephen
|