Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for multiple countifs | Excel Worksheet Functions | |||
Multiple CountIfs | Excel Worksheet Functions | |||
Countifs and multiple columns... | Excel Worksheet Functions | |||
Multiple countifs | Excel Worksheet Functions | |||
COUNTIFs with multiple criteria | Excel Discussion (Misc queries) |