Countifs for multiple worksheets

[JodySmithPharmD]

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

 

[T. Valko]

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)

 

[JodySmithPharmD]

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:n61"),">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]

INDIRECT("'"&A22:A43&"'!M3:M61"),INDIRECT("'"&A22:A43&"'!B1")))

What do you think?

I think COUNTIFS can't be used like that.

I don't see how that formula (if it would work) is less sensitive than using
a separate formula on each sheet and then using a SUM across the sheets. If
anything, that formula would be more apt to fail because the ranges
referenced with INDIRECT:

M3:M61
B1

will *never* change if/when rows/columns are inserted or if you were to move
things around.

 

[Ashish Mathur]

Hi,

I would suggest that we create one range of data split across multiple
worksheets and then use the COUNTIFS() function. To create one range, you
need not copy and paste - that can be automated