Count If combined functions

[Betty H]

Excel 2003. I have a call log with the date call was received in a range
named "Date_Message_Left" fomatted as mm/dd/yyyy, and the Region number in a
range called "Region". The region values are only 1, 2, 3, 4, or 5. I want
a summary of calls to each region that were received in a given week. So if
the region number is 1 and the date is between March 22, 2008 and March 29,
2008, then count it. Some cells in both ranges will be blank until new call
is logged.

I hope this is enough information. Trying to keep it short but still provide
enough info.

 

[Pete_UK]

Try this:

=SUMPRODUCT((Date_Message_Left>=DATE(2008,3,22))*(Date_Message_Left<=DATE(2008,3,29))*(Region=1))

Ensure that the two named ranges have the same number of elements (but
not complete columns).

Hope this helps.

Pete

 

[Betty H]

One thing I forgot to mention... The call log is in a different
workbook/worksheet than the summary. The call log workbook is "Toll Free
Message Log.xls" and the worksheet within the book is called "Master Call Log
2008".
--
Betty H
Salem, OR

Try this:

=SUMPRODUCT((Date_Message_Left>=DATE(2008,3,22))*(Date_Message_Left<=DATE(2008,3,29))*(Region=1))

Ensure that the two named ranges have the same number of elements (but
not complete columns).

Hope this helps.

Pete

 

[Pete_UK]

It's quite a fundamental thing not to mention !!

You could define the named ranges Date_Message_Left and Region in the
workbook with the formula. The general form will be:

'full_path[file_name.xls]sheet_name'!cell_range

The apostrophes are there in case you have any spaces in that string.

Good luck with typing all that out.

A quicker way to do it is to set up your summary sheet within the same
file as the call log, defining those named ranges to suit. Then just
drag the summary sheet away from the call log file and save it as
another file - Excel will automatically adjust the ranges to include
the path and filename for you.

Hope this helps.

Pete