Calculating Bonus Votes in Google Sheets Based on Referrals

Other Questions Google Sheets
1

Hey everyone! I’m working on a Google Sheets document and I need some help. I’ve got a spreadsheet with four columns: User_ID, Referred_by_user_id, Votes, and Referral Votes. I’m trying to figure out how to calculate bonus votes for users based on their referrals.

Here’s what I want to do:

  • Each user can get votes
  • Users get a 20% bonus on votes their referrals receive

I’m looking for a formula that can automatically add 20% of a referred user’s votes to the referrer’s Referral Votes column. Is there a way to do this in Google Sheets? I’m thinking it should be something like:

IF Referred_by_user_id matches User_ID
THEN add 20% of Votes to Referral Votes

Can anyone help me figure out how to set this up? I’m not super experienced with spreadsheets, so any advice would be awesome. Thanks!

2

I’ve tackled a similar challenge in my spreadsheets before. The SUMIF function is indeed a good approach, but you might want to consider using SUMIFS for more flexibility. Here’s a formula that should work:

=SUMIFS(C:C, B:B, A2) * 0.2

This allows you to add more conditions if needed in the future. Also, don’t forget to round the result if you’re dealing with whole votes. You can wrap the formula in ROUND():

=ROUND(SUMIFS(C:C, B:B, A2) * 0.2, 0)

This will give you whole numbers for the bonus votes. Remember to drag the formula down for all users in your User_ID column.

3

Hey SpinningGalaxy! i think u could use SUMIF for this. try something like:

=SUMIF(B:B, A2, C:C) * 0.2

where B is Referred_by, A is User_ID, and C is Votes. This should sum up all votes from referrals and multiply by 20%. hope that helps!

4

I’ve dealt with similar referral systems in my work. One thing to consider is performance as your dataset grows. For large spreadsheets, array formulas can be more efficient. Try this in your Referral Votes column:

=ARRAYFORMULA(IF(LEN(A2:A), MMULT(TRANSPOSE((B2:B=TRANSPOSE(A2:A))*C2:C), ROW(A2:A)^0)*0.2, ))

This calculates all referral votes in one go. It matches referrers to referees, sums up votes, and applies the 20% bonus. The IF statement handles blank rows.

Remember to adjust column references if needed. Also, consider adding error checking and rounding if you want whole numbers. This approach scales well for thousands of rows without slowing down your sheet.