IfF/AND formula for a range of cells

Lioness227

New Member
Joined
Mar 5, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. MacOS
Hi,

I am confused as to which formula would be more appropriate for the below problem I am trying to resolve.

2020 monthly payment tab
I have a list of participants who give £3 per month and can select a product. They can stop participating at any time.
If product A, £1 is allocated to product A budget. And 1 cent is allocated to 53 other products budgets (1 cent each).
Same rule apply to each product.

2020 one off payment tab
I have a list of participants who give whatever they want, for example £100 in December, as a one off.
1% of the £100 will be allocated to each product budget on the month the £100 have been given, so in this case £1 each in December.

last tab where formulating each product budget I have for each month.

=if(and(‘2021-Monthly payment’!C3:C1000=“Product A”,’2020-Monthly payment”!D3:D:1000=3), A60*sum(‘2020-Monthly payment”!D3:D1000), A59*sum(‘2020-Monthly payment”!D3:D1000))+(B62*’2020-one off payment’!C2:C1000)

Data
A59= 0.0033333333
A60=0.33333334
B62=0.01

C3:C1000 is the column where Product name is written
D3:D1000 is the range of monthly participation’s £3 on each line

C2:C1000 is the range of one off payment

QUESTION
Shall I actually use another formula and I am completely wrong using if/and formula? Shall I look at pivot table? I can’t think anymore, been on this one for too long to find au automatic solution rather than doing it manually!

Many thanks in advance (and thankfully it is clear enough, if not please let me know).
 

Attachments

  • 33B95DBB-BDAA-48FF-977E-D8D24370C7F3.jpeg
    33B95DBB-BDAA-48FF-977E-D8D24370C7F3.jpeg
    91.6 KB · Views: 3
  • 1658984C-6A76-4AC7-97BA-CB9555069C3D.jpeg
    1658984C-6A76-4AC7-97BA-CB9555069C3D.jpeg
    103.6 KB · Views: 3

Lioness227

New Member
Joined
Mar 5, 2021
Messages
8
Office Version
  1. 2010
Platform
  1. MacOS
Hi,

i will stop chasing you and find someone else to help no worries, I understand you are busy.

Many thanks,
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Threads
1,130,210
Messages
5,640,869
Members
417,174
Latest member
diegomuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top