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 VBA Training and help » Countif
Countif
Resolved · Medium Priority · Version 2003
Thu 11 Mar 2010, 16:34Delegate Angelo said...
Angelo has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Access Advanced course
Access VBA course
Countif
I have two columns of data starting at A1 & B1. A1 people's age. B1 is Boolean 0 or 1 for whether or not they have a disease (0 = no, 1 = yes). I need to count the number of rows where Age < 30 and Disease = 1. The "30" and the "1" need to refer to cells so that I can change both values without having to change any code. The "<" and "=" will stay the same. Ideally, this would be part of an invented function in VBA such as =ROC(Disease,1,Age,30)= the number of people under 30 with the disease.
Thank you in advance.
Tue 16 Mar 2010, 11:39Trainer Stephen said...
RE: Countif
Hi Angelo
Thanks for your question
You can achieve the above by using a DCOUNT function. The DCOUNT is a database function. You need to create a "criteria range" with the conditions you specify, and then simply use the paste function wizard to build the function.
It has three arguments; the range of your list, the field that you wish to count and the criteria range.
You can vary the criteria by changing the values in the criteria range
I enclose a sample workbook for your information.
Regards
Stephen
Attached files...
Dcount.xls
Training information:
|
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:Create a unique items table from a duplicating table1. Ensure that your list has column headings |
