Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home

arrays - counta formula???

Posted by MC on June 18, 2001 3:52 PM
Hi there,
I'm trying to enter an array that counts the number of cells in a particular column (found on a different worksheet) based on the following criteria:

The values in Column B on the other sheet (contains dates) falls within a certain range and the values in Column G on that other sheet are equal to e.g., "A".

I ended up setting up a separate worksheet to do the calculations, where A1 = 4-jun-01, A2 = 8-jun-01 and A3 = "A".
My formula was therefore:
{=counta(IF('June 01'!B1:B500>=A1,IF('June 01'!B1:B500<=A2,IF('June 01'!G1:G500=A3,'June 01'!B1:B500))))}

Please help!


Re: arrays - counta formula???

Posted by IML on June 18, 2001 4:04 PM

Entering

=SUM((Sheet1!B1:B7>DATEVALUE("01/01/01"))*(Sheet1!B1:B7<DATEVALUE("01/31/01"))*(Sheet1!G1:G7="a"))
followed by control shift enter

will count dates between 1/01/01 and 01/31/01 with an "a" in column G.

good luck


With your data...

Posted by IML on June 18, 2001 4:11 PM

Sorry, just looked at your formula, try
=SUM(('June 01'!B1:B500>=A1)*('June 01'!B1:B500<=A2)*('June 01'!G1:G500=A3))

This will include your start and stop date (and include them). I'm assuming your "A" flag is in A3.

Remember to his control shift enter.


Re: arrays - counta formula???

Posted by MC on June 21, 2001 10:01 AM

Thank you! It works! You're the best!


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.