Hello - I have been stuck on this formula for a few hours. It is the last one I need for this report.
Here is mycurrent formula:
=SUMPRODUCT((1/COUNTIF(CIF,CIF&""))*(--(LEFT(Officer,3)=D2)))
CIF = my rangename for each customer’s ID, which consists of three letters and four numbers.
Officer = myrange name for each officer’s initials, which consists of three letters. I amusing “LEFT” because the data import adds three spaces at the end of theofficer’s initials.
D2 = thespecific officer for this table.
My intent with the formulais to count the number of unique CIF for each Officer. For my test officer, m y result shouldbe 121 but I am getting 119.25. Not sure why it is not a whole number or why itis slightly off. Any advice based on this information?
[FONT="]Here is kind of a sample of what the data looks like – I forgotto put column titles but the first one would be officer and the second would beCIF. There are multiple officers but I just showed one. Just imagine that afterADS is three blank spaces – which is why I need the “LEFT” in there. So thereare actually 180 rows for ADS but I am just showing a sample of what 10 wouldlook like. Based on these 10, my formula should show 8 as an answer. [/FONT]
<tbody>
</tbody>
Here is mycurrent formula:
=SUMPRODUCT((1/COUNTIF(CIF,CIF&""))*(--(LEFT(Officer,3)=D2)))
CIF = my rangename for each customer’s ID, which consists of three letters and four numbers.
Officer = myrange name for each officer’s initials, which consists of three letters. I amusing “LEFT” because the data import adds three spaces at the end of theofficer’s initials.
D2 = thespecific officer for this table.
My intent with the formulais to count the number of unique CIF for each Officer. For my test officer, m y result shouldbe 121 but I am getting 119.25. Not sure why it is not a whole number or why itis slightly off. Any advice based on this information?
[FONT="]Here is kind of a sample of what the data looks like – I forgotto put column titles but the first one would be officer and the second would beCIF. There are multiple officers but I just showed one. Just imagine that afterADS is three blank spaces – which is why I need the “LEFT” in there. So thereare actually 180 rows for ADS but I am just showing a sample of what 10 wouldlook like. Based on these 10, my formula should show 8 as an answer. [/FONT]
| ADS | AAA0112 |
| ADS | AAA0112 |
| ADS | AAA0234 |
| ADS | BAA0123 |
| ADS | BAA1567 |
| ADS | CAA0021 |
| ADS | CAA0021 |
| ADS | DAA0011 |
| ADS | EAA0344 |
| ADS | EAA4559 |
<tbody>
</tbody>
Last edited by a moderator:
