COUNT VALUES DEPENDING ON DATES

Closed
VE - Aug 19, 2009 at 12:47 AM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Aug 21, 2009 at 06:44 AM
Hello,

I am trying to count selected values in one column depending on selected date values in a second column.

Example:

The first column contains the values: 1,2,1,12,31,10,1,7,1
The second column contains dates: 5-8-09,12-8-09,13-8-09,4-8-09,8-8-09,14-8-09,16-7-09,15-8-09,6-8-09

I want to count any cell in the first column that contains 1 or 2 but only if the date falls between 6-8-09 and 12-6-09.

The result would be 3.

What formula can I use?

3 replies

Hi,

Thank you so much, it works!!!

One question though, I should press Ctrl-Shift-Enter rather than Ctrl-Alt-Del at the end?

The '*' between arguments stands for logical 'AND'? MS Help is a bit obscure at times. I couldn't find that anywhere.

Thanks again.

VE
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Aug 20, 2009 at 11:52 AM
Use the formula =SUM((\$B\$1:\$B\$8<=\$A\$12)*(\$B\$1:\$B\$8>=\$A\$11)*(\$A\$1:\$A\$8=2))+SUM((\$B\$1:\$B\$8<=\$A\$12)*(\$B\$1:\$B\$8>=\$A\$11)*(\$A\$1:\$A\$8=1)) Press CTRL + ALT+DEL
to enter the formula
\$A\$12 - end date
\$A\$11 - start date
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Aug 21, 2009 at 06:44 AM
pressing Ctrl+Alt+Del is set to bring up the Windows Security dialog in windows OS
The '*' between arguments stands for multiplication