There's a better way to build this spreadsheet, and it starts with rethinking how you're using IF formulas. You're not alone in feeling stuck, what you're describing is a classic case of using the right tool for the wrong job. The IF function can handle simple yes/no logic, but it falls apart when you need to count how many times a person's initials appear across two separate columns and then apply a different percentage based on that count. The real solution isn't a longer formula, it's a smarter one.
Here's what you actually need: a way to check each role column for a person's initials, count how many times they appear, and then assign either 5% or 2.5% of net income based on whether that count is one or two. That's not a job for IF alone. It's a job for COUNTIF or COUNTIFS. For example, if you have someone's initials in cell A2, you could use something like `=IF(COUNTIF(ContactRange, A2)=1, 0.05*NetIncome, IF(COUNTIF(ContactRange, A2)=2, 0.025*NetIncome, 0))` for the Contact column, and then do the same for the Winner column. But here's the catch: you also need to make sure that if someone is listed as both a Contact and a Winner on the same project, you're not double-counting them. That's where SUMPRODUCT or a helper column can save you.
The clunkiness you're feeling isn't because you're bad at Excel, it's because you're trying to force a single formula to do too much. Break it down. Use a helper column to tag each project row with the number of Contacts and the number of Winners. Then, for each person's initials, use a simple SUMIFS to pull in their share based on those tags. This approach is easier to read, easier to debug, and much easier to scale if your team grows or your project list expands. You don't need a complex array formula or VBA. You need structure.
One more thing: you mentioned the total bonus always equals 10% of net income. That's a useful sanity check. After you set up your formulas, add a row at the bottom that sums all individual bonuses and verify it equals 10% of the net income for each project. If it doesn't, you'll spot the error immediately. That's not just a nice-to-have, it's your safety net. So step back, build a few helper columns, and let COUNTIF and SUMIFS do the heavy lifting. You'll be surprised how quickly the fog lifts.