Changing multiplier based on cell value

Hi!

I'm creating a registration form where you state which days you have attended an activity. The participant receives 4 points for attending on Saturdays and 6 points for Sundays. When they has attended more than seven Saturdays and/or Sundays, they receive 5 points for Saturdays and 7 points for Sundays. This shall not affect the points awarded for attending the first seven Saturdays or Sundays.

A number one is put in column B for participation on that date and in column C the calculation should be. There will be one sheet per month in addition to a summary sheet where you can see the total.

Attachment 885097

Can anyone please help me?

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

You'll need real dates for this.

So, to generate your dates, for example:

=LET(d,DATE(2025,1,1),SEQUENCE(DAY(EOMONTH(d,0)),,d))

and to allocate points:

=IF(F2:F32="","",LOOKUP(WEEKDAY(E2#,2),{1,6,7},{"",4,6}))

See in the attached workbook.

When they has attended more than seven Saturdays and/or Sundays, they receive 5 points for Saturdays and 7 points for Sundays.

This is more complcated when you have all of your months on separate tabs. Is it REALLY necessary to have one tab per month? I have not yet worked this requirement into the formula.