That's what I originally did, but the problem was that multiple users were
adding data to the sheets and the formulas were very sensitive to the adding
of columns etc.

Using the sumproduct and indirect functions I figured out a way to make this
work-almost. The last piece I need is to be able to count a cell if a value
in column m matched a value in cell b1. The formula so far is:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&A22:A43&"'!n3:n6 1"),"0",INDIRECT("'"&A22:A43&"'!m3:m61"),"2/28/10",INDIRECT("'"&A22:A43&"'!m3:m61"),"<4/1/10",INDIRECT("'"&A22:A43&"'!M3:M61"),INDIRECT("'"& A22:A43&"'!B1")))

All but the last bit works. It is complicated! What do you think?

Jody

"T. Valko" wrote:

Can the countifs function be used to count
cells across multiple worksheets?

No.

There is a possible method using SUMPRODUCT but it's VERY complicated and I
would suggest you just put a formula on each sheet in the same cell then sum
that cell across the sheets.

A1 on each sheet holds your COUNTIFS formula.

Then:

=SUM(First:Last!A1)

--
Biff
Microsoft Excel MVP


"JodySmithPharmD" wrote in
message ...
Can the countifs function be used to count cells across multiple
worksheets?
I keep getting a #VALUE! error. I have tried changing the format of the
cells in question but I cannot resolve the error.

Thank you,

Jody


.