COUNTIFS function

[Emily]

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'!D3300, "=Case Mgmt.", 'Policy Log'!E3:E300,
"=Active")

Thank you!

 

[Fred Smith]

When I copied your formula into Excel, it gave me a #Value error. I had to
delete the space after D3300,. (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'!D3300,"*Case Mgmt.*",'Policy
Log'!E3:E300,"*Active*")
which will allow for your labels to be anywhere in the cell.

Fred

 

[Bob Phillips]

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

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

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

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

HTH

Bob