98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Sumproduct - counting distinct values in two columns
Sumproduct - counting distinct values in two columns
Resolved · High Priority · Version 2003
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
Mon 2 Nov 2009, 11:10Trainer Andrew said...
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
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
Mon 2 Nov 2009, 17:04Trainer Andrew said...
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
Tue 24 Nov 2009, 15:04Trainer Stephen said...
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
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. |
Excel tip:Finding your worksheets quicklyThe arrows to the left of the worksheets are used to move between one sheet at a time or first / last worksheet, but if you right click on the arrow buttons it gives you all worksheets in your workbook. |
