Conditional COUNTIF, SUMIF, etc?!?!?!
Posted by Dustin on February 28, 2001 8:04 AM
I am working on a doozy formula here (tough for me anyway) and I cannot figure this one out. If you think it will work faster, feel free to email me a phone number to reach you and I'll gladly call on my dime. I am pulling my hair out on this one.
I need to figure out a way to write a formula on one sheet (Sheet 2 or 3 or 4, etc) to count the number of records on a different sheet (Sheet 1) that meet multiple criteria: a) fall within a date range (date range values found on Sheet 2,3,4,etc) and b) are associated with a specific city.
Sample sheets
(Sheet 1)
Req Number|Status|Date Received|Local Office| ...
0001234 Open 02/04/01 Boston
0009876 Open 02/05/01 Denver
0006543 Open 02/09/01 Tampa
0003579 Open 02/12/01 Denver
(Sheet 2)
Boston
Week Ending Date 1/30/01 - 2/06/01 - 2/13/01
Job Orders Received:
Candidates Submitted:
etc...
(Sheet 3)
Denver
Week Ending Date 1/30/01 - 2/06/01 - 2/13/01
Job Orders Received:
Candidates Submitted:
etc...
(Sheet 4)
Tampa
Week Ending Date 1/30/01 - 2/06/01 - 2/13/01
Job Orders Received:
Candidates Submitted:
etc...
Thanks for any and all help!
Posted by Kurt on February 28, 2001 8:11 AM
You'll need to use an Array formula.
send me an email at the above address, and I'll send you my phone number so I can walk you through it over the phone.
Posted by Aladin Akyurek on February 28, 2001 10:39 AM
I need to figure out a way to write a formula on one sheet (Sheet 2 or 3 or 4, etc) to count the number of records on a different sheet (Sheet 1) that meet multiple criteria: a) fall within a date range (date range values found on Sheet 2,3,4,etc) and b) are associated with a specific city.
Tampa Week Ending Date 1/30/01 - 2/06/01 - 2/13/01 etc...
I'd suggest to select columns of data (excluding the labels) and give each a name (via the Name Box or the option Insert|Define|Name on sheet1.
Lets say that you name the range containing Req Number-values REQNUMS, the range with status-values STATUS, the range of Date Received-values DATES, the range of Local Office-values OFFICES.
These names can be used on all sheets of the workbook in which they are defined.
Lets say that you want a count of 'records' from Sheet1 in some cell of Sheet2. You have 2 dates occupying, say, F1 and F2, and a city in, say, F3. F1, F2, and F3 are your criteria the records have to meet.
Array-enter (that is, hit CONTROL+SHIFT+ENTER at the same time to enter) the following formula:
=SUM((DATES>=F1)*(DATES<=F2)*(OFFICES=F3))
This will give you a count of records/entries on Sheet1 that meet your date and office criteria.
Aladin
Posted by Dustin on February 28, 2001 2:01 PM
Thanks very much Aladin! It worked.
