COUNTIF alternatives for named, non-contiguous range

[nomar116]

I have a bid data set where each month the user has to select several different ranges, each of which are non-contiguous cells containing mostly text. I'm trying to write a VBA script to prompt the user with an input box so they are walked through this selection process for 10-12 different ranges.

My big problem is that, after the user selects the ranges, I would like to then COUNTIF to know how many times a name appears in each of these ranges. I would also like for that formula to work fairly quickly/update automatically anytime the document is edited. I suppose I could create a function of my own, but I've read that can be much slower than Excel's native functions and I don't want to slow this document down anymore than it already is.

I have experimented with nesting INDIRECT inside of COUNTIF but that doesn't work with a named range. Any other suggestions on how I could accomplish this? In my searches I saw some mention of FREQUENCY but that tends to return the same number regardless of what value is searched for.

Once I solve this problem, I might ask for tips designing the InputBox or Userform that gets this data from the user. I'm pretty new to both methods. Right now the biggest complaint I have is my current method the user has to know the key strokes to select a non-contiguous range within the dialog box (i.e. place a comma between selections).

Mahalo for your help!

 

[nomar116]

I should also add that I'm not so sure a named range is the correct way for me to approach this problem.

I need an easy way for the user to look through a large table and select particular cells that will then be counted. That count is taking place in an adjacent table for about 40 rows, with 10-12 columns all having slightly different ranges. The range selection for a given variable / column sometimes has to be updated. Also, when using named ranges the user might realize they've missed a cell and desire to add it into the range easily. Once satisfied with the selected range, the values within those ranges will be changed daily and I need the adjacent "counting" table to auto-update.

I thought using VBA for a named range was the way to go because I could give the user a description of what to do, methodically walking them through a fairly tedious process. BUT it will be pretty annoying if the user gets the range wrong, or the range changes a day later, and they have to go back and reselect every cell within that range. It would be better if they could simply update the existing range.

The way its being done right now is within each cell of the "counting" table I am manually adding COUNTIF statements for each contiguous part of the overall desired selection. The user basically edits this cell, moves around the many contiguous range bins that display when editing, and captures the whole selection. That cell will then display the correct result and the formula is drug across to apply to the 40 or so rows within that counting column.

The method we're using right now of dragging around what I'm calling "range bins" is cumbersome and messy. It's also messy to have to drag and replace the formulas for adjacent cells within th column. I'm just trying to come up with a more presentable and consistent method for the less savy Excel user.

Hopefully this is making sense.