Count Unique Values with Partial Match one column exact match another column

XL 2013

This connection is made with Data > Connections > Microsoft Query.
(I can’t use the Power Query the connection fails.)

This formula returns the Count of Females with LaborCode begins with N during the specified Check Date.

SUMPRODUCT(--(Table_Query_from_XX[CheckDate]=RptCkDt),
--(LEFT(Table_Query_from_XX[LaborDistributionCode],1)="N"),
--(Table_Query_from_XX[***]="F")
)


The problem is an employee check may be cut, cancelled and recut, thereby counting that employee three times instead of once.

How can I count only once occurrence of each female employee (Table_Query_from_XX[EmployeeNumber] ) while maintaining the functionality of LaborCode begins with N?

(Can't seen to get it from a Pivot Table either.)

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

see attached

I don't want to use a helper column because:

1. I can't seem to readily add a calculated field to the MS Query
2. I don't really want to create another table full of calculated columns to analyze the data in the query.

BTW this formula is not in the query table it's on another sheet for the user to view.

Try this:

Please Login or Register  to view this content.

Ctrl Shift Enter

My EmployeeNumber is actually TEXT in the query results so it's failing.

I apologize my sample data did not reflect that.

Hi

Try this formula (I suppose EmployeeNumber in D9:D20)

Formula:

Please Login or Register  to view this content.

Originally Posted by José Augusto

Hi

Try this formula (I suppose EmployeeNumber in D9:D20)

Formula:

Please Login or Register  to view this content.

This does not return the correct result nor does in consider
LEFT(Table_Query_from_XX[LaborDistributionCode],1)="N")

Try this array formula

Formula:

Please Login or Register  to view this content.

Solved modified 63falcondude solution by adding *1 after the [EmployeeNumber]s:

=SUM(--(FREQUENCY(IF((Table_Query_from_XX[CheckDate]=RptCkDt)*(Table_Query_from_XX[***]="F")*(LEFT(Table_Query_from_XX[LaborDistributionCode])="N"),Table_Query_from_XX[EmployeeNumber]*1),Table_Query_from_XX[EmployeeNumber]*1)>0))



PS. how funny, the forum changed my Db fields 3 letter word for Gender to 3 asterisks [***]!