Trouble Using Count Feature for Multiple Criteria

L

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")

S

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.

L

liv4snow

Worked like a charm! that datevalue was key!

Sean Timmons said:
=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.

C

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)