COUNTIFS function
 

Hi,
I have data in two sheets in a workbook (one called "policy log" and the
other "status"). In the policy log sheet I have data in two columns. In one
column I have "case mgmt." and the other "active". In the status sheet I am
trying to calculate how many active case mgmt. policies I have, in other
words, how many times case mgmt. and active appear together. I am using the
following formula and keep getting "0", but I know the value is greater than
0. Any suggestions?

=COUNTIFS('Policy Log'!D3:D300, "=Case Mgmt.", 'Policy Log'!E3:E300,
"=Active")

Thank you!

COUNTIFS function
 

When I copied your formula into Excel, it gave me a #Value error. I had to
delete the space after D3:D300,. (As an aside, never type in a formula,
always cut and paste it -- that way you'll know you're posting the one you
are using).

However, after I fixed the formula, it worked for me.

The problem must be that you don't have exactly "Case Mgmt." and "Active" in
your Policy Log sheet. Are there extraneous spaces in your cells? You might
want to try the following:
=COUNTIFS('Policy Log'!D3:D300,"*Case Mgmt.*",'Policy
Log'!E3:E300,"*Active*")
which will allow for your labels to be anywhere in the cell.

Fred

"Emily" wrote in message
...
Hi,
I have data in two sheets in a workbook (one called "policy log" and the
other "status"). In the policy log sheet I have data in two columns. In
one
column I have "case mgmt." and the other "active". In the status sheet I
am
trying to calculate how many active case mgmt. policies I have, in other
words, how many times case mgmt. and active appear together. I am using
the
following formula and keep getting "0", but I know the value is greater
than
0. Any suggestions?

=COUNTIFS('Policy Log'!D3:D300, "=Case Mgmt.", 'Policy Log'!E3:E300,
"=Active")

Thank you!

COUNTIFS function
 

That works fine for me, in Excel 2007, although you don't need the =

=COUNTIFS('Policy Log'!D3:D300, "Case Mgmt.", 'Policy Log'!E3:E300,
"Active")

This is an Excel 2007/2010 specific function, if you have Excel 2003 use

=SUMPRODUCT(--('Policy Log'!D3:D300="Case Mgmt."),--('Policy
Log'!E3:E300,="Active"))

HTH

Bob

"Emily" wrote in message
...
Hi,
I have data in two sheets in a workbook (one called "policy log" and the
other "status"). In the policy log sheet I have data in two columns. In
one
column I have "case mgmt." and the other "active". In the status sheet I
am
trying to calculate how many active case mgmt. policies I have, in other
words, how many times case mgmt. and active appear together. I am using
the
following formula and keep getting "0", but I know the value is greater
than
0. Any suggestions?

=COUNTIFS('Policy Log'!D3:D300, "=Case Mgmt.", 'Policy Log'!E3:E300,
"=Active")

Thank you!