Unique count of number of weeks worked per month

[alwizardus]

Salutations Gurus

I need to total the number of shows per month that employees worked on during a 12-month period.
Attached is the excel file with the job table in question.

The screenshot below shows the following highlighted dimensions of that table:

• Red, for name of the show;
• Blue, for the number of weeks worked in a month by the person in Job 1 and person in Job 2;
• Yellow, is the result I am trying to total.

The result should only count the number of shows that people worked on. Blank cells should not be counted. Notice, month 12 had no working shows.
Using standard COUNTA() or COUNTIF() will count each populated row of a show twice due to the two jobs that duplicate the rows for each show. This said, I need to count those duplicate rows in any given month as once, if populated; and uniquely as being part of 1 working show; and I can't seem to find the formula to do this correctly.

Thank you

Al

[AliGW]

Administrative Note:

Is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

[AliGW]

If you have upgraded to MS365 or Excel 2021, you can use this copied across:

=COUNTA(UNIQUE(FILTER($C$7:$C$14,D7:D14<>"","")))

[Kaper]

In Excel 2016 (provided you have always 2 jobs per show) this in D16 and copied to the right shall do the task:

Please Login or Register  to view this content.

[alwizardus]

Dear AliGW

Thank you for your input, I just changed the MS version on my profile to indicate: MS365 v2210 - Thank you!

I left one critical piece of data and I did not upload the correct excel file - my apologies.
For the empty rows, I actually have "0" instead of blanks.

I like your formula and I modified the assigned value to the "<>" operator in the Filter() to 0 as opposed to "", and for the most part it did work, but for rows that only have "0", it still counted as 1 as opposed to 0.

This is the modification I made to your formula:

=COUNTA(UNIQUE(FILTER($C$7:$C$14,O7:O14<>0,"")))

Attached is the excel file with this change, if you are able to find the correct formula, I am indebted to your kindness

Thanks again

Al

[AliGW]

Try this:

=LET(f,UNIQUE(FILTER($C$7:$C$14,D7:D14<>0,"")),UNIQUE(IF(f="",0,COUNTA(f))))

You have calculations set to manual - change that to automatic.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[alwizardus]

Thank you for the revision AliGW, works great. Kindly, let me know if the reputation rating came through? I am relatively new to ExcelForum. Al

[alwizardus]

Thank you Kaper, your formula also worked. Much appreciated for your kindness. Al

[AliGW]

Thanks for the rep - very kind.

[Kaper]

Also thanks for the reputation added. Glad it worked for you.