Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home



Countif =name and between date range

Posted by Steven B. on October 10, 2001 7:02 PM
I have a list of supplier names and dates and other data that I need to produce weekly trend charts. I simply want excel to count the number of times a supplier's name occurs in column A, if the date listed in column B is between a given date range. If the countif function could be combined with "AND" it would be so simple, such as:

=countif(a1:a100,"acme machine") AND countif(b1:b100,"between 10/1/01 and <10/9/01")

This would be the intuitive formula, but of course it's not recognized.


Multiconditional Count

Posted by Aladin Akyurek on October 10, 2001 10:52 PM
Steven,

Try

=SUMPRODUCT((A1:A100=C1)*(B1:B100>=C2)*(B1:B100<=C3))

where C1 houses "acme machine", C2 10/1/01, and C3 10/9/01.

Aladin

=========




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.