MichelleC987
New Member
- Joined
- Dec 6, 2017
- Messages
- 5
Good Morning,
I'm looking for some assistance on a formula that will provide a count of unique instances in column B, when Column A is "No". I have success with (=SUMPRODUCT(1/COUNTIF($B$2:$B$9,$B$2:$B$9))), but all is lost when I try to change the COUNTIF to a COUNTIFS to encompass the additional criteria as illustrated below.
This workbook will be used by many users with limited excel background; therefore I would prefer to not include an array formula that would require (Ctrl+Shift+Enter) to calculate. Due to a limitation by the excel file type itself, I cannot apply VBA to this workbook. Any help would be greatly appreciated. Thank you!!
<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>
I'm looking for some assistance on a formula that will provide a count of unique instances in column B, when Column A is "No". I have success with (=SUMPRODUCT(1/COUNTIF($B$2:$B$9,$B$2:$B$9))), but all is lost when I try to change the COUNTIF to a COUNTIFS to encompass the additional criteria as illustrated below.
This workbook will be used by many users with limited excel background; therefore I would prefer to not include an array formula that would require (Ctrl+Shift+Enter) to calculate. Due to a limitation by the excel file type itself, I cannot apply VBA to this workbook. Any help would be greatly appreciated. Thank you!!
Closed | Name | 1/COUNTIF($B$2:$B$9,$B$2:$B$9) | |
No | Test | 0.333333333 | |
Yes | Test1 | 0.25 | |
No | Test | 0.333333333 | |
No | Test2 | 1 | |
No | Test1 | 0.25 | |
YES | Test | 0.333333333 | |
No | Test1 | 0.25 | |
No | Test1 | 0.25 | |
SUMPRODUCT(1/COUNTIF($B$2:$B$9,$B$2:$B$9)) | 3 | ||
SUMPRODUCT(1/COUNTIFS($A$2:$A$9,"No",$B$2:$B$9,$B$2:$B$9)) | 3.83333 |
<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>