Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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



.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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



.



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula for multiple countifs J.Scargill[_2_] Excel Worksheet Functions 4 March 25th 10 02:39 PM
Multiple CountIfs skyflier Excel Worksheet Functions 7 June 6th 09 12:33 PM
Countifs and multiple columns... ajayb Excel Worksheet Functions 1 June 9th 06 03:29 PM
Multiple countifs ozcank Excel Worksheet Functions 2 November 14th 05 11:36 AM
COUNTIFs with multiple criteria Cene K Excel Discussion (Misc queries) 5 October 28th 05 10:43 PM


All times are GMT +1. The time now is 09:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"