Check for Payments Requests for Same Project

aschneiderheinze1025

I have a sheet that collects forms submitted by Program Developers to request payment for work on program development. For the Payee, they can select a [Role] that is either Instructor, Subject Matter Expert (SME), Co-Instructor, or Co-SME. If they choose Co-Instructor or Co-SME, they are asked to enter a [Percentage] of the standard payment that person should receive (e.g., 50%). The default for Instructor and Subject Matter Expert (SME) is 100%.

I want to write a formula that checks if other payment requests have been submitted already for a specific [Program Code]. If another payment request was submitted for a different Payee for the same [Program Code], I want to flag the payment request submissions when

• More than on person has been submitted for payment for the same [Program Code] and both have designated roles as Instructor or Subject Matter Expert (SME), which would mean both would be wrongly paid 100% of the payment amount
• More than one person has been submitted for payment for the same [Program Code] and even if both have designated roles as Co-Instructor or Co-SME, the sums of their designated percentages are over 100%

Example 1: Two payment requests, same Program Code, both 100%

• Payment #1 (2-15-2025), John Doe, Program Code 5001, "Instructor", 100%
• Payment #2 (2-18-2025), Jessica Little, Program Code 5001, "Instructor", 100%

Example 2: Two payment requests, same Program Code, same Payee

• Payment #1 (2-18-2025), Milo Sutton, Program Code 5025, "Instructor", 100%
• Payment #2 (3-2-2025), Milo Sutton, Program Code 5025, "Instructor", 100%

Example 3: Two payment requests, same Program Code, sum of % is > 100

• Payment #1 (3-5-2025), Mya Reynolds, Program Code 5018, "Co-SME", 50%
• Payment #2 (3-19-2025), Phuong Taylor, Program Code 5018, "Co-SME", 70%

Example 4: Two payment requests, same Program Code, one is full SME, one Co-SME

• Payment #1 (3-1-2025), Daniel Glugman, Program Code 5006, "SME", 100%
• Payment #2 (3-14-2025), Linda Simpson, Program Code 5006, "Co-SME", 50%

Example 5: Two payment requests, same Program Code, different roles

• Payment #1 (3-7-2025), Lauren Klappst, Program Code 5014, "Instructor", 100%
• Payment #2 (3-22-2025), Peter Parker, Program Code 5014, "Co-SME", 50%
• Payment #3 (3-23-2025), Ismael Franklin, Program Code 5014, "Co-SME", 50%

Both payment requests for Examples 1, 2, 3, and 4 should be flagged. Example 5 should NOT be flagged even thought there are different roles, there is one instructor at 100% and two Co-SMEs at 50% each.

Any ideas?

Soumitra Bhowmick

Example 1,2,3 are clear that for same Program code and Same Role, total can't go beyond 100%.

Not clear about Example 4 since role are different, so by your logic, that should get passed. Please explain?

aschneiderheinze1025

@Soumitra Bhowmick Example 4 shows that two different payment requests were submitted, but one was for a full SME (at 100%) and one for a Co-SME (50%). I would guess the first one was submitted incorrectly. It probably should have also been Co-SME (50%). For each project, there is either one SME (100%) or multiple Co-SMEs (percentages totaling 100%). So this should be flagged to avoid paying someone at 100% and another person at 50%.

How do you "search" within the same sheet to find the rows that have the same [Program Code], and then do the "analysis" to determine if there are multiple payment requests for the same [Program Code], the total percentage (for Payees with the same [Role} doesn't exceed 100%?

Example 5 illustrates how its OK if there are multiple payment requests for same [Program Code] and its OK that there are payments requests for different roles. But looking more closely, these requests are all OK because the total for Instructor is 100% (and there are no Co-Instructors), and there is no single SME but two Co-SMEs and their total is 100%.

Soumitra Bhowmick

For now I could only come up with this, maybe with few other Clarification, I can try out something more

=IF(AND(COUNTIFS([Program Code]:[Program Code], [Program Code]@row, Role:Role, Role@row) > 1, SUMIFS([Final %]:[Final %], [Program Code]:[Program Code], [Program Code]@row, Role:Role, Role@row) > 100), "Duplicate", "")

But I would really like to get some more insight on this? Is it possible to connect with in Person say Google meet?

Paul Newcome

Are you able to provide some screenshots for context?

You should be able to just use a SUMIFS to add up all of the percentages and flag any rows where the total percentage for a project code is above 100% (below is for a flag type column).

=IF(SUMIFS(Percentage:Percentage, [Program Code]:[Program Code], @cell = [Program Code]@row) > 1, 1)

aschneiderheinze1025

Sure.

I created T-ProgramCode and T-Role, which just copy whatever was in [Program Code] and [Role], just to make this screenshot.

The formula in [TEST Duplicates}:

=IF(AND(COUNTIFS([Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], [T-Role]@row) > 1, SUMIFS(Percentage:Percentage, [Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], [T-Role]@row) > 100), "Duplicate", "")

This works for identifying duplicate [Program Code] and duplicate [Role] and checks that the sum percentage among the duplicates is <100. If you change the percentage for the bottom two (5003, Co-SME) to "50" each, "Duplicate" goes away for those rows. This addresses Example 1 and Example 3.

For Example 2, I changed the name of the second Co-SME (near bottom). I want it to say "Duplicate" because even though same [Program Code], [Role], and [Percentage} <= to 100, its a duplicate payment request because the [Payee] is the same. Note: The Payee may be the same for a Program Code if they are both the Instructor (or a Co-Instructor) and the Subject Matter Expert (SME) (or Co-SME). They just can't be the same Role more than once for a Program Code.

Example 4 I feel is the hardest. I changed the Payees to be different, but now the [Role] is different. A program can't have a Subject Matter Expert (SME) who is always paid 100% and a Co-SME who in this case is also to be paid but at 50%. So, the "check" needs to look for duplicate [Program Code] and instances when there is a "Subject Matter Expert (SME)" and "Co-SME" (or "Instructor" and "Co-Instructor") because there is never a case when this would happen. It was probably an error that Taylor Flanders was entered as a Subject Matter Expert (SME). If they choose "Subject Matter Expert (SME)" in the form, they aren't asked a percentage because its always 100%. If the person submitting the form chose "Co-SME" they probably would have put 50%.

aschneiderheinze1025

I may have it. I'm testing it out now. I explain below how I wrote a formula for each Example(s), then combined them (thanks to ChatGPT). Let me know if there is a more efficient way to do this, and I'll follow-up if I find it doesn't work completely for the examples.

=IF(OR(AND(COUNTIFS([Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], [T-Role]@row) > 1, SUMIFS(Percentage:Percentage, [Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], [T-Role]@row) > 100), COUNTIFS([Program Code]:[Program Code], [Program Code]@row, [T-Payee]:[T-Payee], [T-Payee]@row, [T-Role]:[T-Role], [T-Role]@row) > 1, AND(OR([T-Role]@row = "Co-SME", [T-Role]@row = "Subject Matter Expert (SME)"), COUNTIFS([Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], "Subject Matter Expert (SME)") > 0, COUNTIFS([Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], "Co-SME") > 0), AND(OR([T-Role]@row = "Co-Instructor", [T-Role]@row = "Instructor"), COUNTIFS([Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], "Instructor") > 0, COUNTIFS([Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], "Co-Instructor") > 0)), "Duplicate", "")

I wrote a formula for each example:

————————————-

Example 1: Two payment requests, same Program Code, both 100%

• Payment #1 (2-15-2025), John Doe, Program Code 5001, "Instructor", 100%
• Payment #2 (2-18-2025), Jessica Little, Program Code 5001, "Instructor", 100%

Example 3: Two payment requests, same Program Code, sum of % is > 100

• Payment #1 (3-5-2025), Mya Reynolds, Program Code 5018, "Co-SME", 50%
• Payment #2 (3-19-2025), Phuong Taylor, Program Code 5018, "Co-SME", 70%
  

=IF(AND(COUNTIFS([Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], [T-Role]@row) > 1, SUMIFS(Percentage:Percentage, [Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], [T-Role]@row) > 100), "Duplicate", "")

————————————-

Example 2: Two payment requests, same Program Code, same Payee

• Payment #1 (2-18-2025), Milo Sutton, Program Code 5025, "Instructor", 100%
• Payment #2 (3-2-2025), Milo Sutton, Program Code 5025, "Instructor", 100%

=IF(COUNTIFS([Program Code]:[Program Code], [Program Code]@row, [T-Payee]:[T-Payee], [T-Payee]@row, [T-Role]:[T-Role], [T-Role]@row) > 1, "Duplicate", "")

————————————-

Example 4: Two payment requests, same Program Code, one is full SME, one Co-SME

• Payment #1 (3-1-2025), Daniel Glugman, Program Code 5006, "SME", 100%
• Payment #2 (3-14-2025), Linda Simpson, Program Code 5006, "Co-SME", 50%

For SMEs:

=IF(AND(OR([T-Role]@row = "Co-SME", [T-Role]@row = "Subject Matter Expert (SME)"), COUNTIFS([Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], "Subject Matter Expert (SME)") > 0, COUNTIFS([Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], "Co-SME") > 0), "Duplicate", "")

For Instructors:

=IF(AND(OR([T-Role]@row = "Co-Instructor", [T-Role]@row = "Instructor"), COUNTIFS([Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], "Instructor") > 0, COUNTIFS([Program Code]:[Program Code], [Program Code]@row, [T-Role]:[T-Role], "Co-Instructor") > 0), "Duplicate", "")