Trouble Using Count Feature for Multiple Criteria

[liv4snow]

Help! I am trying to create a formula in Excel to count the number of
occurances that two particular variables both appear. I.E. Start Date and #
of Days.

Example:

Column A Column B
Start Date # of Days
1/7/09 31
1/14/09 14
1/21/09 23
2/4/09 38
1/7/09 46
2/4/09 16
1/7/09 54
1/7/09 17

I have tried following SUM PRODUCT formula and several other variances but
can't seem to get it work. Any ideas?

=SUM((Sheet1!$A$3:$A$9="1/07/2009")*IF((Sheet1!$B$3:$B$9!<31),1))
=COUNTIF((Sheet1!$A$3:$A$9),"1/07/2009")*(Sheet1!$B$3:$B$9<"31")
=SUMPRODUCT((Sheet1!$A$3:$A$9),"1/07/2009")*(Sheet1!$B$3:$B$9<"31")

 

[Sean Timmons]

=SUMPRODUCT(--(Sheet1!$A$3:$A$9=datevalue("1/07/2009")),--(Sheet1!$B$3:$B$9<31))
would return the number of times the date is Jan 7th of 2009 and the # of
days is less than 31.

 

[liv4snow]

Worked like a charm! that datevalue was key!

=SUMPRODUCT(--(Sheet1!$A$3:$A$9=datevalue("1/07/2009")),--(Sheet1!$B$3:$B$9<31))
would return the number of times the date is Jan 7th of 2009 and the # of
days is less than 31.

 

[Chip Pearson]

that datevalue was key!

Note that DATEVALUE uses the system's Locale setting to interpret the
date, so if there is any possibility that a user on a system with a
different date format would use the workbook, DATVALUE may return the
"wrong" date. E.g., in the US, 9/2/2009 is 2-Sept-2009, while in
Europe, 9/2/2009 is 9-February-2009.

As a general rule, you should use the DATE function rather than
DATEVALUE if you can. E.g,

DATE(2009,9,2)

returns the date 2-Sept-2009 regardless of the user's locale settings.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)