How to count rows matching a multi condition in an entire column 2

[estafford]

I need to count rows that match multiple conditions from two or more columns.

example: count rows where column A = "string" and column B = "string".

I can get the count of a single condition on a complete column (A:A). But I cannot get it to work using multiple conditions on complete columns. It will only work using a specific range (A5:A10) within a column.

Is there a way to do this on an entire column.

 

[DaleWatson123321]

Especially when getting into "multiple" conditions, the most POWERFUL functions (and most often overlooked) are Excel's "database functions" - e.g. =DCOUNTA, =DSUM, etc.

While VERY powerful, they take a little getting used to - i.e. in setting up the criteria, etc.

A preferable method of demonstrating their power, and use, is for me to email you an example. Better still, if you want to email me a file with your requirements, I can "tailor make" the solution to fit. If you happen to have sensitive data in your file, perhaps you could replace it with fictitious data, and possibly "scale down" the size of the file if preferred.

I hope this can help.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[RobBroekhuis]

Dale, a star for you not for this particular problem, but for repeatedly bringing Excel's database functions to my conscious attention. Now that I know they exist, I can think of all kinds of neat things to do with them!
Rob

 

[DaleWatson123321]

Rob,

Thanks for the STAR.

I just have to reciprocate with a STAR to you, for having taken the initiative to help "break the mold" created by Microsoft and other educators by their AVOIDANCE of this VERY POWERFUL component of Excel.

With your comment... "Now that I know they exist, I can think of all kinds of neat things to do with them!", it's a strong "endorsement" - and should help convince others that indeed they should take a SERIOUS look at this POWERFUL feature.

And I feel the need (again) to point out to others that this "database functionality" of Excel ALSO includes the ability to extract (to a SEPARATE sheet) the "data behind the totals" created with the database formulas.

For example, I've developed a model where all the user has to do to extract the &quot;data behind any total&quot;, is to &quot;click a macro button&quot; when the cursor is placed on any such total. The result is that the detail (for that total) is extracted from the database to a SEPARATE sheet, and headings changed to reflect the type of data extracted - i.e. the &quot;type&quot; and &quot;timeframe&quot; for the data. The extracted data is also prepared for printing. All the user has to do is hit <Control> P <Enter>.

As for Microsoft, they haven't even bothered to fix the TWO BUGS that cause users to AVOID using this POWERFUL feature. And they've had several years to do so. Performance or what !!!

Again, to inform those who might not have read one of my other postings... A BUG will surface and PREVENT one from extracting data to a SEPARATE sheet - but ONLY IF you attempt to do so using Excel's menu: Data - Filter - Advanced Filter. The BUG is an ERROR message that say's Excel cannot extract data to a SEPARATE sheet. Ironically it's the ERROR message &quot;itself&quot; that prevents this from happening. Using VBA, there is NO problem.

The other BUG can surface when one places the &quot;criteria&quot; used by these database formulas (or extraction of data via VBA) on the SAME sheet. Again, an ERROR message tells you it's NOT possible. However, again, it IS possible using VBA. And AGAIN, it's ironic that placing the criteria on a SEPARATE sheet is actually PREFERABLE - because when they are placed on the SAME sheet, Excel &quot;gets confused&quot;. This confusion is possibly caused from Excel being programmed to attempt to &quot;think&quot; or &quot;anticipate&quot; what the user is attempting to do. The bottom line, is that Excel behaves MUCH better when the criteria are placed on a SEPARATE sheet.

Thanks again, Rob, for taking a stand. ;-)

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Perhaps I was... ... Here's a slight correction to the LAST paragraph - a change from &quot;SAME&quot; to &quot;SEPARATE&quot; in the first sentence.

The other BUG can surface when one places the &quot;criteria&quot; used by these database formulas (or extraction of data via VBA) on a SEPARATE sheet. Again, an ERROR message tells you it's NOT possible. However, again, it IS possible using VBA. And AGAIN, it's ironic that placing the criteria on a SEPARATE sheet is actually PREFERABLE - because when they are placed on the SAME sheet, Excel &quot;gets confused&quot;. This confusion is possibly caused from Excel being programmed to attempt to &quot;think&quot; or &quot;anticipate&quot; what the user is attempting to do. The bottom line, is that Excel behaves MUCH better when the criteria are placed on a SEPARATE sheet.