Thinking further, it might be helpful to detail a query table solution.
The file must be saved and the data must be in a list - that is, header titles to each fields. Data is in A5:D200. So in A4:D4 create field headers. I used {"First", "Second", "Third", "Fourth"}
And give the whole list a name. I used "MyData" as the name, and it covers A4:D200 (Shortcut from a cell in the list, select using CTRL-SHIFT-8 then CTRL-F3 to enter the name.)
The next steps require MS Query to be installed, and this is easily done the first time it is needed. On the main menu go Data, Get External Data, New Database Query.
Choose "Excel files" at the first tab, then select the workbook, when the wizard starts, select the data table (can be just the field/s you want or all at once. If there are multiple tables, you can select them here. HINT: this can lead to joined tables a la Access, and results like VLOOKUP without formulas, for example.)
Continuing with the wizard, filter the result set with "First" field equals to "C", "Second" field begins with "GPH", "Third" is greater than 15 and "Fourth" is equal to "Y". These can be all selected at the "Filter Data" tab. Then continue to the "Finish" tab but select "View data or edit query in Microsoft Query" then "Finish".
This will lead to MS Query and a view of the results set. All matching records are showing. The applied criteria/filters are showing.
The SQL can be viewed via either View, SQL or the "SQL" toolbar button.
The SQL for me being,
SELECT MyData.First, MyData.Second, MyData.Third, MyData.Fourth
FROM `H:\Book3`.MyData MyData
WHERE (MyData.First='C') AND (MyData.Second Like 'GPH%') AND (MyData.Third>15) AND (MyData.Fourth='Y')
Because we want a count of the records rather than the result set, I edited the SQL directly in the window to,
SELECT COUNT(*)
FROM `H:\Book3`.MyData MyData
WHERE (MyData.First='C') AND (MyData.Second Like 'GPH%') AND (MyData.Third>15) AND (MyData.Fourth='Y')
And, further to have a better final name,
SELECT COUNT(*) AS [MyAnswer]
FROM `H:\Book3`.MyData MyData
WHERE (MyData.First='C') AND (MyData.Second Like 'GPH%') AND (MyData.Third>15) AND (MyData.Fourth='Y')
The result set is now the required count.
Now either hit the "Return Data" button (shows a door) which is immediately left of the SQL button or via menu File, Return Data to Excel. And put the answer in a worksheet. With header.
Like a pivot table, this doesn't refresh automatically. Refresh by selecting a cell in the table and then (like a PT) Data, Refresh.
To update via code, something like
ActiveSheet.QueryTables(1).Refres BackgroundQuery:=False
Various thoughts. Doesn't accept dynamic ranges (such as with OFFSET, COUNTA). Can be more complex - multiple table joins. Queries closed files. Can feed into a pivot table - so that massive data sources can be filtered before the PT.
This is introductory. You can do lots with Query Tables. Look at the data range property (such as right click when a cell in the query table is selected. You can set refresh times, retain formats on refresh, fill down formulas on RHS, etc)
You can cut & paste the SQL for editing outside the window.
You can manipulate the SQL via VBA. It is only simple text manipulation. Be careful with the punctuation!
You can create query tables via VBA, it is straightforward.
Suggest you fiddle around and read the built in help.
Watch that the file references as given above are hard coded to when you created the query. This can be edited, though.
Gives you the answer, and some much more complex ones, without a formula.
I know I've typed a fair bit. However, it is pretty straightforward to get started due to the Wizard.
HTH
Fazza
