Countifs with "or" embedded?

[Reed Robinson]

I'm trying to perfect a spreadsheet for work that will count the number of patients who have up-to-date annual paperwork of two types (call them Exam 1 and Exam 2). One of the main functions of the sheet is to alert providers of patients whose paperwork is about to expire. Therefore, the sheet uses two "date of last exam" for each of the two exams (let's say they're in columns A & B), and then has two status columns (C & D), both returning either "Absent" (if no exam has been done), "Overdue" (if the exam is out of date), "Due in 30 Days" (if the due date is within 30 days), or "Current" (if the exam is up to date) for each of the two exams.

Based on this paradigm, the data in status columns C and D can be boiled down into either "Compliant" (consisting of "Current" and "Due in 30 Days" or "Non-Compliant" (consisting of "Absent" and "Overdue".



What I'm having problems with is writing the formula that will allow me to count the number of patients that are compliant on BOTH exams without adding extra cells (that I know I can do). In other words, I'd prefer not to add a set columns that boil my data down to an intermediary level to make counting my desired result easier. Instead, I'd like to embed the necessary arguments into my formula.



Given an array of 2 columns and 11 rows that should return a value of "4", The way that seems like it SHOULD work is to say:

=COUNTIFS(C1:C11,OR(C1="Current",C1="Due in 30 Days")=true),D211,OR(D1="Current",D1="Due in 30 Days")=true)



What I keep ending up with for a result is "0". I do not get an error, so if there's a syntax error, excel is reading it as something else.



Anyone have any ideas, or should I suck it up and take the easy (but less elegant) way out?



Thanks!



Reed


Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET All Rows Editable AJAX-Enabled DataGrid
http://www.eggheadcafe.com/tutorial...74-c23e95eb6f00/aspnet-all-rows-editable.aspx

 

[Ron Rosenfeld]

I'm trying to perfect a spreadsheet for work that will count the number of patients who have up-to-date annual paperwork of two types (call them Exam 1 and Exam 2). One of the main functions of the sheet is to alert providers of patients whose paperwork is about to expire. Therefore, the sheet uses two "date of last exam" for each of the two exams (let's say they're in columns A & B), and then has two status columns (C & D), both returning either "Absent" (if no exam has been done), "Overdue" (if the exam is out of date), "Due in 30 Days" (if the due date is within 30 days), or "Current" (if the exam is up to date) for each of the two exams.

Based on this paradigm, the data in status columns C and D can be boiled down into either "Compliant" (consisting of "Current" and "Due in 30 Days" or "Non-Compliant" (consisting of "Absent" and "Overdue".



What I'm having problems with is writing the formula that will allow me to count the number of patients that are compliant on BOTH exams without adding extra cells (that I know I can do). In other words, I'd prefer not to add a set columns that boil my data down to an intermediary level to make counting my desired result easier. Instead, I'd like to embed the necessary arguments into my formula.



Given an array of 2 columns and 11 rows that should return a value of "4", The way that seems like it SHOULD work is to say:

=COUNTIFS(C1:C11,OR(C1="Current",C1="Due in 30 Days")=true),D211,OR(D1="Current",D1="Due in 30 Days")=true)



What I keep ending up with for a result is "0". I do not get an error, so if there's a syntax error, excel is reading it as something else.



Anyone have any ideas, or should I suck it up and take the easy (but less elegant) way out?



Thanks!



Reed


Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET All Rows Editable AJAX-Enabled DataGrid
http://www.eggheadcafe.com/tutorial...74-c23e95eb6f00/aspnet-all-rows-editable.aspx

If I understand you correctly, a patient is compliant if both column C and
column D equals either "current" or "due in 30 days".

You could try something like:

=SUMPRODUCT((C1:C11="current")+(C1:C11="due in 30 days"),
(D111="current")+(D111="due in 30 days"))

--ron