Countifs for multiple worksheets
 

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

Countifs for multiple worksheets
 

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

Countifs for multiple worksheets
 

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


.

Countifs for multiple worksheets
 

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.

--
Biff
Microsoft Excel MVP


"JodySmithPharmD" wrote in
message ...
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


.

Countifs for multiple worksheets
 

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

--
Regards,

Ashish Mathur
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