Software

Ask something
Discussion Locked

Excel question

0 helpful
1 reply

by ronan.anglin

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

In an excel file, i have 2 worksheets called "A" and "B". "A" has a load of data on the sheet with a filter turned on. B is a blank worksheet. In "A" i filter it down for things that happened between 2 dates, say >10-1-06 and < 10-2-06 since date is a field. This reduces the data from a 1000 lines to a hundred lines. Can I automatically format a cell in the second work sheet to count the number of lines between those dates.

I assume i'll be putting in some kind of fomula into the formula bar? Maybe something like....=COUNT 'A!' where Date >10-1-06 & < 10-2-06. Can I do the whole thing automatically without having to filter A?

1 reply

by rdk55

Give this formula a try. No prior filtering should be required...

=COUNTIF(RANGE,">="&DATEVALUE("10-1-06"))-COUNTIF(RANGE,">"&DATEVALUE("10-2-06"))

Replace both occurances of "RANGE" with your actual range on your spreadsheet.

You may have to change the way the datevalues are entered as well depending on your regional settings. (On my system, this works with DATEVALUE("01/10/2006").

R.K.

This discussion is closed to new replies.