| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF Formula in Multiple worksheets.
I need to use the COUNTIF formula to count items in 52 different worksheets.
I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
Laurent Longre may have a solution for you:
http://xcell05.free.fr/ look down the page for a download for Countif.3d Oops - just tried that and unfortunately the link on her page for that file is the same as for the file just above it, COUNTDIFF. YOu might try contacting her and asking for a copy of the file, and a corrected link. "onlyinsyder" wrote: I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
onlyinsyder wrote:
I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent Hi Brent, you can use the free Add-in MOREFUNC.XLL by Laurent Longre. You can download it he http://xcell05.free.fr/english/ -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
What do you want to count and where is it? What are your sheet names? Since
you have 52 sheets I'm guessing they represent weeks? Like Week1, Week2 etc ? Biff "onlyinsyder" wrote in message ... I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
yes, weeks. but i believe that my problem is solved by downloading Laurent
Longre's plug-ins. still, it wouldnt be a bad idea for microsoft the think about updating some functions to include 3d references. brent "Biff" wrote: What do you want to count and where is it? What are your sheet names? Since you have 52 sheets I'm guessing they represent weeks? Like Week1, Week2 etc ? Biff "onlyinsyder" wrote in message ... I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
You have 52 sheets named Week 1, Week 2, Week 3,.......Week 52
You want to count how many values in the range A1:A10 are greater than 50: =SUMPRODUCT(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&"'!A1:A10"),"50")) Biff "onlyinsyder" wrote in message ... yes, weeks. but i believe that my problem is solved by downloading Laurent Longre's plug-ins. still, it wouldnt be a bad idea for microsoft the think about updating some functions to include 3d references. brent "Biff" wrote: What do you want to count and where is it? What are your sheet names? Since you have 52 sheets I'm guessing they represent weeks? Like Week1, Week2 etc ? Biff "onlyinsyder" wrote in message ... I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
actually, biff, it was much eaiser to download the plugin an use the
counif.3d function. it works great. but thanks anyway. by the way, maybe you sould try it out. brent "Biff" wrote: You have 52 sheets named Week 1, Week 2, Week 3,.......Week 52 You want to count how many values in the range A1:A10 are greater than 50: =SUMPRODUCT(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&"'!A1:A10"),"50")) Biff "onlyinsyder" wrote in message ... yes, weeks. but i believe that my problem is solved by downloading Laurent Longre's plug-ins. still, it wouldnt be a bad idea for microsoft the think about updating some functions to include 3d references. brent "Biff" wrote: What do you want to count and where is it? What are your sheet names? Since you have 52 sheets I'm guessing they represent weeks? Like Week1, Week2 etc ? Biff "onlyinsyder" wrote in message ... I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
by the way, maybe you sould try it out.
Why use an add-in to do something that you can do with built-in functions? Biff "onlyinsyder" wrote in message ... actually, biff, it was much eaiser to download the plugin an use the counif.3d function. it works great. but thanks anyway. by the way, maybe you sould try it out. brent "Biff" wrote: You have 52 sheets named Week 1, Week 2, Week 3,.......Week 52 You want to count how many values in the range A1:A10 are greater than 50: =SUMPRODUCT(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&"'!A1:A10"),"50")) Biff "onlyinsyder" wrote in message ... yes, weeks. but i believe that my problem is solved by downloading Laurent Longre's plug-ins. still, it wouldnt be a bad idea for microsoft the think about updating some functions to include 3d references. brent "Biff" wrote: What do you want to count and where is it? What are your sheet names? Since you have 52 sheets I'm guessing they represent weeks? Like Week1, Week2 etc ? Biff "onlyinsyder" wrote in message ... I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
because it would take years for me to write one formula, not to mention the
other 51 formulas. just let it go biff. the problem is solved. not everything has to be done your way. Brent "Biff" wrote: by the way, maybe you sould try it out. Why use an add-in to do something that you can do with built-in functions? Biff "onlyinsyder" wrote in message ... actually, biff, it was much eaiser to download the plugin an use the counif.3d function. it works great. but thanks anyway. by the way, maybe you sould try it out. brent "Biff" wrote: You have 52 sheets named Week 1, Week 2, Week 3,.......Week 52 You want to count how many values in the range A1:A10 are greater than 50: =SUMPRODUCT(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&"'!A1:A10"),"50")) Biff "onlyinsyder" wrote in message ... yes, weeks. but i believe that my problem is solved by downloading Laurent Longre's plug-ins. still, it wouldnt be a bad idea for microsoft the think about updating some functions to include 3d references. brent "Biff" wrote: What do you want to count and where is it? What are your sheet names? Since you have 52 sheets I'm guessing they represent weeks? Like Week1, Week2 etc ? Biff "onlyinsyder" wrote in message ... I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
not everything has to be done your way.
Excuse me? I showed you how to do it. Use it or not. Biff "onlyinsyder" wrote in message ... because it would take years for me to write one formula, not to mention the other 51 formulas. just let it go biff. the problem is solved. not everything has to be done your way. Brent "Biff" wrote: by the way, maybe you sould try it out. Why use an add-in to do something that you can do with built-in functions? Biff "onlyinsyder" wrote in message ... actually, biff, it was much eaiser to download the plugin an use the counif.3d function. it works great. but thanks anyway. by the way, maybe you sould try it out. brent "Biff" wrote: You have 52 sheets named Week 1, Week 2, Week 3,.......Week 52 You want to count how many values in the range A1:A10 are greater than 50: =SUMPRODUCT(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&"'!A1:A10"),"50")) Biff "onlyinsyder" wrote in message ... yes, weeks. but i believe that my problem is solved by downloading Laurent Longre's plug-ins. still, it wouldnt be a bad idea for microsoft the think about updating some functions to include 3d references. brent "Biff" wrote: What do you want to count and where is it? What are your sheet names? Since you have 52 sheets I'm guessing they represent weeks? Like Week1, Week2 etc ? Biff "onlyinsyder" wrote in message ... I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
because it would take years for me to write one formula,
not to mention the other 51 formulas. You don't need to write 52 formulas. You write 1 formula and it calculates all 52 sheets. Biff "onlyinsyder" wrote in message ... because it would take years for me to write one formula, not to mention the other 51 formulas. just let it go biff. the problem is solved. not everything has to be done your way. Brent "Biff" wrote: by the way, maybe you sould try it out. Why use an add-in to do something that you can do with built-in functions? Biff "onlyinsyder" wrote in message ... actually, biff, it was much eaiser to download the plugin an use the counif.3d function. it works great. but thanks anyway. by the way, maybe you sould try it out. brent "Biff" wrote: You have 52 sheets named Week 1, Week 2, Week 3,.......Week 52 You want to count how many values in the range A1:A10 are greater than 50: =SUMPRODUCT(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&"'!A1:A10"),"50")) Biff "onlyinsyder" wrote in message ... yes, weeks. but i believe that my problem is solved by downloading Laurent Longre's plug-ins. still, it wouldnt be a bad idea for microsoft the think about updating some functions to include 3d references. brent "Biff" wrote: What do you want to count and where is it? What are your sheet names? Since you have 52 sheets I'm guessing they represent weeks? Like Week1, Week2 etc ? Biff "onlyinsyder" wrote in message ... I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
just leave it alone already. i fixed the problem. do you live with your mom
or somethin? you have nothing better to do? look, i tried your little formula, and it doesnt work as well, ok? so just drop it. "Biff" wrote: because it would take years for me to write one formula, not to mention the other 51 formulas. You don't need to write 52 formulas. You write 1 formula and it calculates all 52 sheets. Biff "onlyinsyder" wrote in message ... because it would take years for me to write one formula, not to mention the other 51 formulas. just let it go biff. the problem is solved. not everything has to be done your way. Brent "Biff" wrote: by the way, maybe you sould try it out. Why use an add-in to do something that you can do with built-in functions? Biff "onlyinsyder" wrote in message ... actually, biff, it was much eaiser to download the plugin an use the counif.3d function. it works great. but thanks anyway. by the way, maybe you sould try it out. brent "Biff" wrote: You have 52 sheets named Week 1, Week 2, Week 3,.......Week 52 You want to count how many values in the range A1:A10 are greater than 50: =SUMPRODUCT(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&"'!A1:A10"),"50")) Biff "onlyinsyder" wrote in message ... yes, weeks. but i believe that my problem is solved by downloading Laurent Longre's plug-ins. still, it wouldnt be a bad idea for microsoft the think about updating some functions to include 3d references. brent "Biff" wrote: What do you want to count and where is it? What are your sheet names? Since you have 52 sheets I'm guessing they represent weeks? Like Week1, Week2 etc ? Biff "onlyinsyder" wrote in message ... I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
No good deed goes unpunished.
Biff wrote: because it would take years for me to write one formula, not to mention the other 51 formulas. You don't need to write 52 formulas. You write 1 formula and it calculates all 52 sheets. Biff "onlyinsyder" wrote in message ... because it would take years for me to write one formula, not to mention the other 51 formulas. just let it go biff. the problem is solved. not everything has to be done your way. Brent "Biff" wrote: by the way, maybe you sould try it out. Why use an add-in to do something that you can do with built-in functions? Biff "onlyinsyder" wrote in message ... actually, biff, it was much eaiser to download the plugin an use the counif.3d function. it works great. but thanks anyway. by the way, maybe you sould try it out. brent "Biff" wrote: You have 52 sheets named Week 1, Week 2, Week 3,.......Week 52 You want to count how many values in the range A1:A10 are greater than 50: =SUMPRODUCT(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&"'!A1:A10"),"50")) Biff "onlyinsyder" wrote in message ... yes, weeks. but i believe that my problem is solved by downloading Laurent Longre's plug-ins. still, it wouldnt be a bad idea for microsoft the think about updating some functions to include 3d references. brent "Biff" wrote: What do you want to count and where is it? What are your sheet names? Since you have 52 sheets I'm guessing they represent weeks? Like Week1, Week2 etc ? Biff "onlyinsyder" wrote in message ... I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
LOL!
You're welcome. Thanks for the feedback! Biff "onlyinsyder" wrote in message ... just leave it alone already. i fixed the problem. do you live with your mom or somethin? you have nothing better to do? look, i tried your little formula, and it doesnt work as well, ok? so just drop it. "Biff" wrote: because it would take years for me to write one formula, not to mention the other 51 formulas. You don't need to write 52 formulas. You write 1 formula and it calculates all 52 sheets. Biff "onlyinsyder" wrote in message ... because it would take years for me to write one formula, not to mention the other 51 formulas. just let it go biff. the problem is solved. not everything has to be done your way. Brent "Biff" wrote: by the way, maybe you sould try it out. Why use an add-in to do something that you can do with built-in functions? Biff "onlyinsyder" wrote in message ... actually, biff, it was much eaiser to download the plugin an use the counif.3d function. it works great. but thanks anyway. by the way, maybe you sould try it out. brent "Biff" wrote: You have 52 sheets named Week 1, Week 2, Week 3,.......Week 52 You want to count how many values in the range A1:A10 are greater than 50: =SUMPRODUCT(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&"'!A1:A10"),"50")) Biff "onlyinsyder" wrote in message ... yes, weeks. but i believe that my problem is solved by downloading Laurent Longre's plug-ins. still, it wouldnt be a bad idea for microsoft the think about updating some functions to include 3d references. brent "Biff" wrote: What do you want to count and where is it? What are your sheet names? Since you have 52 sheets I'm guessing they represent weeks? Like Week1, Week2 etc ? Biff "onlyinsyder" wrote in message ... I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
Biff,
Thank you for your post. I prefer to actually learn formulas instead of downloading some kind of shortcut or cheat. :) Found this thread while searching for how to do CountIf in multiple worksheets. Thanks again for your help!! "Biff" wrote: LOL! You're welcome. Thanks for the feedback! Biff "onlyinsyder" wrote in message ... just leave it alone already. i fixed the problem. do you live with your mom or somethin? you have nothing better to do? look, i tried your little formula, and it doesnt work as well, ok? so just drop it. "Biff" wrote: because it would take years for me to write one formula, not to mention the other 51 formulas. You don't need to write 52 formulas. You write 1 formula and it calculates all 52 sheets. Biff "onlyinsyder" wrote in message ... because it would take years for me to write one formula, not to mention the other 51 formulas. just let it go biff. the problem is solved. not everything has to be done your way. Brent "Biff" wrote: by the way, maybe you sould try it out. Why use an add-in to do something that you can do with built-in functions? Biff "onlyinsyder" wrote in message ... actually, biff, it was much eaiser to download the plugin an use the counif.3d function. it works great. but thanks anyway. by the way, maybe you sould try it out. brent "Biff" wrote: You have 52 sheets named Week 1, Week 2, Week 3,.......Week 52 You want to count how many values in the range A1:A10 are greater than 50: =SUMPRODUCT(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&"'!A1:A10"),"50")) Biff "onlyinsyder" wrote in message ... yes, weeks. but i believe that my problem is solved by downloading Laurent Longre's plug-ins. still, it wouldnt be a bad idea for microsoft the think about updating some functions to include 3d references. brent "Biff" wrote: What do you want to count and where is it? What are your sheet names? Since you have 52 sheets I'm guessing they represent weeks? Like Week1, Week2 etc ? Biff "onlyinsyder" wrote in message ... I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF Formula in Multiple worksheets.
Yes, I remember that thread very well!
Oh, well! Biff "Treesy" wrote in message ... Biff, Thank you for your post. I prefer to actually learn formulas instead of downloading some kind of shortcut or cheat. :) Found this thread while searching for how to do CountIf in multiple worksheets. Thanks again for your help!! "Biff" wrote: LOL! You're welcome. Thanks for the feedback! Biff "onlyinsyder" wrote in message ... just leave it alone already. i fixed the problem. do you live with your mom or somethin? you have nothing better to do? look, i tried your little formula, and it doesnt work as well, ok? so just drop it. "Biff" wrote: because it would take years for me to write one formula, not to mention the other 51 formulas. You don't need to write 52 formulas. You write 1 formula and it calculates all 52 sheets. Biff "onlyinsyder" wrote in message ... because it would take years for me to write one formula, not to mention the other 51 formulas. just let it go biff. the problem is solved. not everything has to be done your way. Brent "Biff" wrote: by the way, maybe you sould try it out. Why use an add-in to do something that you can do with built-in functions? Biff "onlyinsyder" wrote in message ... actually, biff, it was much eaiser to download the plugin an use the counif.3d function. it works great. but thanks anyway. by the way, maybe you sould try it out. brent "Biff" wrote: You have 52 sheets named Week 1, Week 2, Week 3,.......Week 52 You want to count how many values in the range A1:A10 are greater than 50: =SUMPRODUCT(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&"'!A1:A10"),"50")) Biff "onlyinsyder" wrote in message ... yes, weeks. but i believe that my problem is solved by downloading Laurent Longre's plug-ins. still, it wouldnt be a bad idea for microsoft the think about updating some functions to include 3d references. brent "Biff" wrote: What do you want to count and where is it? What are your sheet names? Since you have 52 sheets I'm guessing they represent weeks? Like Week1, Week2 etc ? Biff "onlyinsyder" wrote in message ... I need to use the COUNTIF formula to count items in 52 different worksheets. I know that this formula will not work in counting multiple worksheets. Anyone know of a quick workaround? I dont want to get stuck typing 52 different SUM formulas. Thanks, Brent |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying a formula to multiple worksheets | Excel Worksheet Functions | |||
| Adding same cells across multiple worksheets | Excel Worksheet Functions | |||
| Adding multiple worksheets | Excel Worksheet Functions | |||
| Countif multiple worksheets | Excel Worksheet Functions | |||
| Countif formula with multiple criteria ie >30 and <60? | Excel Worksheet Functions | |||
Linear Mode
