Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countifs function equivalent for Excel 2002 (XP) | Excel Worksheet Functions | |||
Excel 2007 countifs function problem | Excel Discussion (Misc queries) | |||
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
COUNTIFS Function | Excel Worksheet Functions | |||
translating countifs function to Excel 2003 | Excel Worksheet Functions |