IfF/AND formula for a range of cells

[Lioness227]

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).

 

[Lioness227]

Hi,

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

Many thanks,