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),D2:D11,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
ADO Recordset and Stored Procedures
http://www.eggheadcafe.com/tutorials...nd-stored.aspx

There's an error in the formula.

It should've read:
=COUNTIFS(C1:C11,OR(C1="Current",C1="Due in 30 Days")=true),D1:D11,OR(D1="Current",D1="Due in 30 Days")=true)

I'd just typed it in wrong. The error isn't in my sheet, so the problem persists:(



Reed Robinson wrote:

Countifs with "or" embedded?
14-Dec-09

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),D2:D11,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

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Create the Dynamic HTML window using Java Script
http://www.eggheadcafe.com/tutorials...ic-html-w.aspx

There's an error in this corrected formula because the parentheses don't
match. *Never* type in a formula. *Always* copy and paste it.

Regards,
Fred

"Reed Robinson" wrote in message
...
There's an error in the formula.

It should've read:
=COUNTIFS(C1:C11,OR(C1="Current",C1="Due in 30
Days")=true),D1:D11,OR(D1="Current",D1="Due in 30 Days")=true)

I'd just typed it in wrong. The error isn't in my sheet, so the problem
persists:(



Reed Robinson wrote:

Countifs with "or" embedded?
14-Dec-09

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),D2:D11,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

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Create the Dynamic HTML window using Java Script
http://www.eggheadcafe.com/tutorials...ic-html-w.aspx