MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.