excel 2007 vba countifs across multiple sheets 1.5 million rows

[largeselection]

Hello,

Basically I have an issue that is probably best served with a database. I have an excel sheet that is 1.5 million rows (spread across 20 tabs). I don't have access to a database program. I need to do something that would be simple with a pivot table, but I can't pivot 1.5 million rows across multiple sheets to my knowledge.

So I figured out a way I could get the answers I need using countifs.

The issue that I'm having is that I don't know how to define the ranges of all worksheets.

What I'd like to accomplish (VBA or otherwise) would be something like this: If the cell in column B (across all worksheets) = reference cell A3 and the cell in column C (again across all worksheets) = "<1" then count it.

I can do this in one sheet easily using countifs, but I don't know how to set the range as every column B in the workbook.

I know this is confusing, so please ask questions if you have any

 

[Gerald Higgins]

I tried this myself in 2003 and couldn't get it to work using COUNTIF, I suspect it only works on a single worksheet.
But, why not try a series of COUNTIFs (or similar), one for each sheet ? Then SUM the COUNTIFs ?
The COUNTIFs do not have to be located on the worksheet they refer to, they just seem to have to refer to one worksheet at a time.

 

[Domenic]

List the sheet names in a range of cells, let's say G1:G20, then try the following...

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$G$1:$G$20&"'!B:B"),A3,INDIRECT("'"&$G$1:$G$20&"'!C:C"),"<1"))

 

[largeselection]

Domenic,

Thanks for the reply! This is actually the method I ended up using. Works wonders.