Counting Multiple Columns but excluding duplicates

[BrokenCloud]

Hello! First time posting here! I have been observing from afar for a while but I have hit a snag which required me to reach out for help directly!

I am trying to count information regarding employees during timekeeping for a small company. I have multiple columns and I want to exclude duplicate values. For example, I have last names, first names and job designations in separate columns. However, our timekeeping program tallies types of times in distinct rows. So I want to exclude duplicate values, for example - the following formula works correctly:

=SUMPRODUCT((1/COUNTIFS('Import From Total Hours Summary'!A2:A142,'Import From Total Hours Summary'!A2:A142,'Import From Total Hours Summary'!B2:B142,'Import From Total Hours Summary'!B2:B142))*('Import From Total Hours Summary'!H2:H142="Driver"))

What I am trying to do is expand the range so that I can past data sheets of differing sizes, however I get the following error:

I'm wondering if anyone has any advice on how to correct this? The formula works as is if there are no blank values, however I am unable to figure it out past this point.

Thank you in advance!

-J

 

[awoohaw]

Congratulations on your first post to the forum, welcome.

If you define your data as a table you can paste only the data at the last row of the exsiting table (or the first row if you delete all but the header) and the table references will only read to the last populated row. But, I really do not understand why you are counting the same matching column in the COUNTIFS (you maybe doing it correctly but I can't figure out what it is your are trying to count).

So, define your range as a table, and use this formula:
(assuming you name your table as "EmployeeTable").

=SUMPRODUCT(
(1/COUNTIFS(
EmployeeTable[Lastname],
EmployeeTable[Lastname],
EmployeeTable[Firstname],
EmployeeTable[Firstname]))
*
(EmployeeTable[Home Department Desc]="Driver")
)

One thing for the future. Mr. Excel Forum has a tool called xl2bb add in (link below) which allows you to paste actual mini worksheets with formulas and resulting values. As well as some other helpful features that are in the worksheets. Pasting images of your worksheets is really not very helpful in terms of getting a fast response. If you cannot use an add in, then post your data as a table. Images are the worst. And I'm not really sure you gave enough examples in your range for the forum to really work out your formula - I am making a guess you want the COUNTIF done the way you do. If you look at the posts in the forum that get the fastest responses from forum member you will see that they have posted via xl2bb or a table. The reason... the forum member needs to recreate your scenario from scratch. AND, they may misunderstand one or more of your conditions. This wastes everyone's time. So, in the future help the forum help you.

As such, I'm not even sure the solution i give above helps your question. If it does then I made a good guess. If it doesn't, please post some data that the forum can copy.

Regardless, best wishes!