Count rows based upon multiple criteria

[indyman]

I need to run lots of metrics on a spreadsheet based upon multiple criteria. The data willl be refreshed often, so I will have to dynamically dtermine and assign ranges. No problem there.

However, I need to include multiple criteria from a few columns. Say from Column C I need to count all rows where the cell = "Car", or "Ball", or "Bike". But I only want to count those cells if Column D contains either "Red", or "Yellow" or "Blue". There will actually be 5 to 10 values in each of the columns I will search, and never more than 3 columns at a time.

I have a limitation in that I do not have the ability to install the Analysis Toolpak, so the database functions are not an option. Anyone who can help guide me on a method to use? Thanks in advance.

[Bob Phillips]

=SUMPRODUCT(--(ISNUMBER(MATCH(C2:C200,{"Car","Ball","Bike"},0))),--(ISNUMBER(MATCH(D2200,{"Red","Yellow","Blu

etc.

[indyman]

=SUMPRODUCT(--(ISNUMBER(MATCH(C2:C200,{"Car","Ball","Bike"},0))),--(ISNUMBER(MATCH(D2200,{"Red","Yellow","Blu

etc.

THanks for the quick response. I'll give that a try. in SUMPRODUCT, do I have to use cell references, or can I use a range cariable?

[indyman]

I get "Expected list separator or " error when I enter this line.

intFilterCount = SUMPRODUCT(--(ISNUMBER(MATCH(E2:E200,{"Car","Ball","Bike"},0))) with the : highlighted.

I havve defined intFilterCount as a Long variable

[Bob Phillips]

Where are you using intFilterCount? BTW, this is not VBA.

[indyman]

OK. I am fairly new to VBA. I'm confused about your BTW. If it is not VBA, what may I ask, is it?

[Bob Phillips]

It is a worksheet formula.

[indyman]

I now understand that. I appreciate your trying to help. I am confused though. Since I am posting on a forum called VBA Express, I thought the assumption was that help is being requested on a macro, or VBA. I also used the terminology "dynamically determine and assign ranges" would also be a clue that I was not trying to write a formula in a cell. So you can probably understand my confusion that the first answer to my post was to provide me with a worksheet formula. That confusion is even more now that I see in the Excel Help that SUMPRODUCT is listed under “List of Worksheet Functions Available to Visual Basic”.

But now I know that I was going down the wrong path at least. You learn more every day. Thanks.

[Bob Phillips]

What you actually said was ... so I will have to dynamically dtermine and assign ranges ... which can just as easily be done in Excel as in VBA. You may think that just becuae the forum is called VBAExpress that you will only get VBA solutions, but some of us are not so blind as to belive that VBAis the only way. The formula that I gave you will work, with dynamic ranges or static ranges, and will be far more efficient that any VBA that hyou can write, or even I can write, will be.

The advantage of posting to a credible forum like this is that we think for ourselves, we don't neceesarily pander to your pre-conceptions.

[indyman]

You know, there is more to a forum than building your post counter to a higher #. Credible forums don't let their contributors treat a post as if the author were an idiot. I believe that VBA is the only way in dealing with dynamic data, not because I am blind, but because I don't know any better.

You are right about one thing, you do not pander to my preconceptions (whether wrong or right). You only pander to your own. You offer a formula, but try to belittle me for not knowing it to be a worksheet formula - instead of offering advice (which is what credible forums do) on how to properly use it in VBA. Hopefully you are not representative of the VBAX community.

[Bob Phillips]

Well, I have certainly learnt one thing here, there is absolutely no point in me wasting my time again trying to help you, as you obviously know best, and I obviously do not know enough to be able to solve your problems.

What I gave you was the BEST solution based upon the information given. You are free to to take my solution, or ignore, but if you chooes to criticise me as you did in #8 for being so 'dumb' as to not realise that because you wanted dynamic ranges it had to be VBA (another thing you are wrong in), then you will get a response and you should be big enough to listen. True, the solution wasn't VBA, but you might find quite a few solutions posted here that are not VBA, it is a very bad idea to assume that VBA is the best solution when you don't even have the skills to build the solution

[mdmackillop]

I would think that almost everyone here has learned from XLD's posts. I certainly have. And while we may have different approaches to some questions, he presents excellent solutions.
With regard to your original question, it was not clear that a VBA solution only was required. When this is the case, or the opposite, it's simplest to state this. That's what I do.