# IF statement. Dates with different outcomes

Microsoft Excel 2003 (full product)
April 7, 2010 at 02:38:26
Specs: Windows XP
 I am trying to extract some data on dates to a dashboard to present it in a pie chart. I have a list of dates relating to when something was tested. I need to establish which ones were tested in the last 3,6,12,12+ months, and those that haven't been tested (Blanks) then extract it onto another sheet on the document as simple numbers. Can anyone help?

See More: IF statement. Dates with different outcomes

#1
April 7, 2010 at 04:24:27
 Hi,For this example the dates, including some blank cells, are in the range B2:B200,Use this formula to return the number of dates no more than 30 days before today:`=SUMPRODUCT((\$B\$2:\$B\$200>=NOW()-30)*(1))`To count the number of dates in certain ranges, use this formula:`=SUMPRODUCT((\$B\$2:\$B\$200>=NOW()-60)*(\$B\$2:\$B\$200"")*(1))`adjusted to match the end of the last range of dates. Note that this formula excludes counting empty cells.Finally to count empty cells use this formula:`=SUMPRODUCT((\$B\$2:\$B\$200="")*(1))`If these formulas were on another sheet and the dates are on Sheet1, you get something like this:`=SUMPRODUCT((Sheet1!\$B\$2:\$B\$200>=NOW()-60)*(Sheet1!\$B\$2:\$B\$200

Report •

#2
April 7, 2010 at 05:17:53
 Thank you very much, That worked perfectly. cheers!

Report •

#3
April 7, 2010 at 06:46:31
 You're welcomeRegardsHumar

Report •
Related Solutions