Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count with multiple critirea
I have a worksheet that contains a date range in column B, and a reason code
(text) in column E. I am trying to count the number of times a reson code is used for each date. I.E on 1/30/09 how many cells in column E contain reason "XYZ". Each day a file is created with these exact column headings. The name of the files is always that days date (Pulled file today so the name of the file would be todays date). I want to be able to enter a formula in a seperate workbook that will give me these counts for each days file by changing the name of the file it references. I hope someone understands this and can help me, cuz I have spent days messing with array formulas, COUNTIF, COUNT IF, SUM IF, SUMIF and none of them will work, they all give me the counts of both that meet the date and the reason code together. That is not what I am trying to accomplish. SOMONE PLEASE HELP ME!!! THANK YOU IN ADVANCE!!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count with multiple critirea
The only thing I needed to add was a cell that holds the date you want to
collect the data for. So I put that in Q1, then next to it in R1 you can put this formula: =SUMPRODUCT(--($B$2:$B$100=Q1),--($E$2:$E$100="XYZ")) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "MANYHEADACHES" wrote: I have a worksheet that contains a date range in column B, and a reason code (text) in column E. I am trying to count the number of times a reson code is used for each date. I.E on 1/30/09 how many cells in column E contain reason "XYZ". Each day a file is created with these exact column headings. The name of the files is always that days date (Pulled file today so the name of the file would be todays date). I want to be able to enter a formula in a seperate workbook that will give me these counts for each days file by changing the name of the file it references. I hope someone understands this and can help me, cuz I have spent days messing with array formulas, COUNTIF, COUNT IF, SUM IF, SUMIF and none of them will work, they all give me the counts of both that meet the date and the reason code together. That is not what I am trying to accomplish. SOMONE PLEASE HELP ME!!! THANK YOU IN ADVANCE!!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count with multiple critirea
This formula will count all the XYZ in column E of a file called
"01-30-2009"...edit as needed: =COUNTIF([01-30-2009.xls]Sheet1!$E$2:$E$100,"XYZ") -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: The only thing I needed to add was a cell that holds the date you want to collect the data for. So I put that in Q1, then next to it in R1 you can put this formula: =SUMPRODUCT(--($B$2:$B$100=Q1),--($E$2:$E$100="XYZ")) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "MANYHEADACHES" wrote: I have a worksheet that contains a date range in column B, and a reason code (text) in column E. I am trying to count the number of times a reson code is used for each date. I.E on 1/30/09 how many cells in column E contain reason "XYZ". Each day a file is created with these exact column headings. The name of the files is always that days date (Pulled file today so the name of the file would be todays date). I want to be able to enter a formula in a seperate workbook that will give me these counts for each days file by changing the name of the file it references. I hope someone understands this and can help me, cuz I have spent days messing with array formulas, COUNTIF, COUNT IF, SUM IF, SUMIF and none of them will work, they all give me the counts of both that meet the date and the reason code together. That is not what I am trying to accomplish. SOMONE PLEASE HELP ME!!! THANK YOU IN ADVANCE!!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count with multiple critirea
If you're talking about changing the entry in a cell to a particular date and
having the adjacent cell tell you how many XYZ are in that file's column E, the only way to do that is with an INDIRECT function. INDIRECT would let you insert a cell's value into the middle of a formula to dynamically change it. The problem is that you can't use INDIRECT to reference a closed workbook, which i am sure is what you are wanting to do, right? All the dated workbooks are closed? If the dated workbooks are open, then this would work with the cell A2 housing the name of the workbook to open: =COUNTIF(INDIRECT("["&A2&".xls]Sheet1!$E$2:$E$100"),"XYZ") To get around the problem, you will have to use an Addin called MoreFunc. The addin function is called Indirect.ext and you can read up on it he http://xcell05.free.fr/morefunc/engl...direct.ext.htm Read here for the overview of the entire array of addins that come with this, as well as find the download link: http://xcell05.free.fr/morefunc/english/index.htm -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: This formula will count all the XYZ in column E of a file called "01-30-2009"...edit as needed: =COUNTIF([01-30-2009.xls]Sheet1!$E$2:$E$100,"XYZ") -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: The only thing I needed to add was a cell that holds the date you want to collect the data for. So I put that in Q1, then next to it in R1 you can put this formula: =SUMPRODUCT(--($B$2:$B$100=Q1),--($E$2:$E$100="XYZ")) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "MANYHEADACHES" wrote: I have a worksheet that contains a date range in column B, and a reason code (text) in column E. I am trying to count the number of times a reson code is used for each date. I.E on 1/30/09 how many cells in column E contain reason "XYZ". Each day a file is created with these exact column headings. The name of the files is always that days date (Pulled file today so the name of the file would be todays date). I want to be able to enter a formula in a seperate workbook that will give me these counts for each days file by changing the name of the file it references. I hope someone understands this and can help me, cuz I have spent days messing with array formulas, COUNTIF, COUNT IF, SUM IF, SUMIF and none of them will work, they all give me the counts of both that meet the date and the reason code together. That is not what I am trying to accomplish. SOMONE PLEASE HELP ME!!! THANK YOU IN ADVANCE!!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count with multiple critirea
Hi,
You can't use COUNTIF for two conditions but in 2007 you can use COUNTIFS as follows: =COUNTIFS('[Quick Parts.xlsm]Conditional Formatting'!$B$1:$B$11,A8,'[Quick Parts.xlsm]Conditional Formatting'!$E$1:$E$11,B8) Where B8 contains the date and A8 the reason code you want to count. FYI - It is bad newsgroup practice to post the same question twice. -- If this helps, please click the Yes button Cheers, Shane Devenshire "MANYHEADACHES" wrote: I have a worksheet that contains a date range in column B, and a reason code (text) in column E. I am trying to count the number of times a reson code is used for each date. I.E on 1/30/09 how many cells in column E contain reason "XYZ". Each day a file is created with these exact column headings. The name of the files is always that days date (Pulled file today so the name of the file would be todays date). I want to be able to enter a formula in a seperate workbook that will give me these counts for each days file by changing the name of the file it references. I hope someone understands this and can help me, cuz I have spent days messing with array formulas, COUNTIF, COUNT IF, SUM IF, SUMIF and none of them will work, they all give me the counts of both that meet the date and the reason code together. That is not what I am trying to accomplish. SOMONE PLEASE HELP ME!!! THANK YOU IN ADVANCE!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count count multiple rows | Excel Discussion (Misc queries) | |||
To count the data using multiple criteria in multiple columns | New Users to Excel | |||
Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
Multiple Count IF 's | Excel Discussion (Misc queries) |