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
Hi,
The basic idea would be =SUMPRODUCT(--('[Lesson06.xls]United States'!$B$4:$B$51=A3),--('[Lesson06.xls]United States'!$E$4:$E$51=B3)) Here I have assumed the Date you want to find is in A3 and the code in B3, change as needed. You could add the INDIRECT function to allow you to enter the workbook name in a cell and use that in the formula, but the downside is that INDIRECT only works against Open files. (Of course you could download a free copy of Open Office which does allow the INDIRECT function to work against closed files. Even Lotus 1-2-3 version 1 (1982) allowed this. -- 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 with multiple critirea | Excel Worksheet Functions | |||
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 |