Count of dates linked to additional criteria

  • Thread starter Thread starter Darren
  • Start date Start date
D

Darren

Hi.
I currently have the following function set up to count
the number of records based on dates:

=SUM((Sheet1!A1:A5575>=DATE(2003,4,1))*(Sheet1!
A1:A5575<=DATE(2003,4,30)))

In the same worksheet there are fields populated with data
for which I would like to know the count of this data
against the above function.
For example: How many "data" for April 2003 (above
function).

I tried using IF statements within the same function, but
I'm having no joy :(

Any help greatly appreciated :)
 
Darren said:
Hi.
I currently have the following function set up to count
the number of records based on dates:

=SUM((Sheet1!A1:A5575>=DATE(2003,4,1))*(Sheet1!
A1:A5575<=DATE(2003,4,30)))

In the same worksheet there are fields populated with data
for which I would like to know the count of this data
against the above function.
For example: How many "data" for April 2003 (above
function).

I tried using IF statements within the same function, but
I'm having no joy :(

Any help greatly appreciated :)
Click to expand...

I am not sure I quite follow what you are asking. Do you mean that you want
a count of data in some column when the date in column A is as in the above?
If so, you need a formula such as:
=SUM((Sheet1!A1:A5575>=DATE(2003,4,1))*(Sheet1!A1:A5575<=DATE(2003,4,30))*(S
heet1!G1:G5575<>""))
You will know that these formulas must be array-entered. An alternative that
doesn't require array-entering is to use SUMPRODUCT rather than SUM:
=SUMPRODUCT((Sheet1!A1:A5575>=DATE(2003,4,1))*(Sheet1!A1:A5575<=DATE(2003,4,
30))*(Sheet1!G1:G5575<>""))
 
Hi Darren!

Try the following as a bases that you can expand upon:

=SUMPRODUCT((Sheet1!A1:A5575>=DATE(2003,4,1))*(Sheet1!A1:A5575<=DATE(2
003,4,30))*(Sheet1!B1:B5575=7))
Or:
=SUMPRODUCT((MONTH(Sheet1!A1:A5575)=4)*(YEAR(Sheet1!A1:A5575)=2003)*(S
heet1!B1:B5575=7))

The structures within these formulas may be regarded as implicit IF
statements that return 0 or 1. Only if (in the example) all three are
true will the product of the trio be 1.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top