COUNTIF single criteria across multiple sheets??

[ijohnnycakes]

After poking around for quite I haven't found THE definitive answer one
way or another to my problem.

I have in a single workbook a series of worksheets, each representing a
different month. Within each month's worksheet are columns for things
like catagories, costs, percentages, etc...

In addition to these monthly worksheets, I have a worksheet that simply
displays the number of ocurrances of each catagory across all the
monthly worksheets (or at least I'd like to have such a count).

I've read a number of posts related to multiple criteria, but have not
yet read one exclusively about a COUNTIF function counting across
multiple sheets.

Since I'm doing something pretty mundane and low level I just assumed
that this would be easy. As its turning out, either its not, or I'm
just too new to Excel to figure it out. In any case, I sure would
apprciate it if someone here could share the answer to my little
problem. Thanks!

 

[Earl Kiosterud]

Johnny,

It may well be that it's time to combine the sheets into one, using a month
column, if the same fields are in the various sheets. Doesn't take long to
combine them. Having data in separate sheets precludes the use of a lot of
terrific tools, like pivot tables, autofilters (easy way to reduce it to the
month you're interested in at any particular time), etc. Simple COUNTIFs,
or a pivot table, would give you the counts you want in this case.

 

[Harlan Grove]

...
...

I have in a single workbook a series of worksheets, each representing a
different month. Within each month's worksheet are columns for things
like catagories, costs, percentages, etc...

In addition to these monthly worksheets, I have a worksheet that simply
displays the number of ocurrances of each catagory across all the
monthly worksheets (or at least I'd like to have such a count). ...
Since I'm doing something pretty mundane and low level I just assumed
that this would be easy. As its turning out, either its not, or I'm
just too new to Excel to figure it out. In any case, I sure would
apprciate it if someone here could share the answer to my little
problem. Thanks!

Very dangerous assumption to make when it comes to Excel. Excel lacks many types
of 3D functionality that (former and concurrent) users of other speradsheets
take for granted. One could develop the impression that Microsoft would rather
revolutionize WordArt than actually add mundane spreadsheet features to Excel.

Anyway, if you had a list of worksheet names either in a range or as an array
constant, you could try something like

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Jan","Feb","Mar","Apr","May","Jun",
"Jul","Aug","Sep","Oct","Nov","Dec")&"'!A1:Z100"),"=whatever"))