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!
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
Posted by IML on June 18, 2001 4:11 PM
With your data...
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.
Posted by MC on June 21, 2001 10:01 AM
Thank you! It works! You're the best!
