Google Sheets / Excel : SUM values between sheets IF they are not blank, up to a MAX value
Budget: $10 – $30 USD
I have a Google Sheet that can be seen here:
https://tinyurl.com/4wwed972
In A2 I would like the shortest formula that can perform the following:
- In B2, C2, D2 - if the value is not blank, we consider it for the calculation, but only up to the maximum value specified.
For example:
Under 'Zoo' we have 3, so it is considered for the calculation. But in the !Memberships sheet we can see that we have 2 visits per year, with a corresponding dollar value of 25. So for Zoo, we would calculate 50.
If, under 'Zoo' we had 1 it would calculate 25. If it's 2 we would calculate 50. If it's 3 we would calculate 75. But if it's 4 or more, it would still calculate 75 because 3 is our maximum, based on what is entered under 'Zoo'.
Under 'Gym' we have 2, so it is considered for the calculation. In the !Memberships sheet we can see that we have 3 visits per year, with a corresponding dollar value of 45. However, we are only allowed to calculate up to a maximum of 2. So for Gym we would calculate 90.
Under 'Science' it is blank, it is not considered for calculation.
The formula for A2 provides the SUM of whatever the 3 totals work out to. In this example, Zoo is calculated as 50, and Gym is 90, so it would show 140 in A2.
NOTE: The names of the columns (Zoo, Gym, Science) do not match the rows in !Memberships exactly, so that should be considered.
https://tinyurl.com/4wwed972
In A2 I would like the shortest formula that can perform the following:
- In B2, C2, D2 - if the value is not blank, we consider it for the calculation, but only up to the maximum value specified.
For example:
Under 'Zoo' we have 3, so it is considered for the calculation. But in the !Memberships sheet we can see that we have 2 visits per year, with a corresponding dollar value of 25. So for Zoo, we would calculate 50.
If, under 'Zoo' we had 1 it would calculate 25. If it's 2 we would calculate 50. If it's 3 we would calculate 75. But if it's 4 or more, it would still calculate 75 because 3 is our maximum, based on what is entered under 'Zoo'.
Under 'Gym' we have 2, so it is considered for the calculation. In the !Memberships sheet we can see that we have 3 visits per year, with a corresponding dollar value of 45. However, we are only allowed to calculate up to a maximum of 2. So for Gym we would calculate 90.
Under 'Science' it is blank, it is not considered for calculation.
The formula for A2 provides the SUM of whatever the 3 totals work out to. In this example, Zoo is calculated as 50, and Gym is 90, so it would show 140 in A2.
NOTE: The names of the columns (Zoo, Gym, Science) do not match the rows in !Memberships exactly, so that should be considered.