Count If combined functions


B

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.
 
Ad

Advertisements

P

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
 
B

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


Pete_UK said:
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
 
Ad

Advertisements

P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Netwrokdays in VBA 1
Countif 2
COUNT formula help 9
ARRAY with countif 6
count 7
Spurious Formula Omits Adjacent Cells 2
EXCEL COUNTING HELP 3
Windows 7 and DateTimePicker control? 0

Top