Help with a countif with multiple conditions

Excel Excel 2007
February 21, 2011 at 05:19:02
Specs: Windows 7
I am trying to build an Excel 2007 spreadsheet to track on a weekly basis the status of specific activities. Out of an undetermined raw list of rows, I need to check for any Noncompliance Issue (text field in column C) that is not Closed (text field in Column E - could be Open, Action Plan Approved, or Date Extension Pending) and are more than 7 days out and not more than 14 days. I have tried multiple things but nothing seems to work. Currently, I have a separate tab where I define the possible text and dates to shorten the equation but still no luck.

So: P2 has today's date, P3 has today's + 7, and P4 has today's +14, R2 has text "Noncompliance Issue", U3 has text "Open", U4 has text "Action Plan Approved", and U5 has text "Date Extension Pending" With those variables set, I tried:

=COUNTIFS('All Records for Dept_February_1'!C:C,R2,'All Records for Dept_February_1'!E:E,U3,'All Records for Dept_February_1'!I:I, COUNTIF('All Records for Dept_February_1'!I:I,"<=" &E1)-COUNTIF('All Records for Dept_February_1'!I:I,"<" &F1))


=SUM(('All Records for Dept_February_1'!C:C="Noncompliance Issue")*('All Records for Dept_February_1'!E:E="Open")*('All Records for Dept_February_1'!I:I<=P4)*('All Records for Dept_February_1'!I:I>P3))

So, what am I doing wrong? Let me know if I need to provide any additional detail.

See More: Help with a countif with multiple conditions

February 21, 2011 at 08:30:14
Try the SUMPRODUCT function.

Don't rely on the Excel help files for SUMPRODUCT as they are very limited.

Google the function and read some of the info you find.

This might be a good start.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question