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]
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]ADS[/TD]
[TD]AAA0112[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]AAA0112[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]AAA0234[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]BAA0123[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]BAA1567[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]CAA0021[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]CAA0021[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]DAA0011[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]EAA0344[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]EAA4559[/TD]
[/TR]
</tbody>[/TABLE]
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]
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]ADS[/TD]
[TD]AAA0112[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]AAA0112[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]AAA0234[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]BAA0123[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]BAA1567[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]CAA0021[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]CAA0021[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]DAA0011[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]EAA0344[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]EAA4559[/TD]
[/TR]
</tbody>[/TABLE]
Last edited by a moderator:
