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

 

[jtakw]

Hi,

Looking at the formula you're using (or trying to use), I don't think it'll work, you have made arrays inside the AND statement, but it won't work as-is, even if the formula is array entered (CSE).
I can't make any suggestions at this time, as you only uploaded pics/screenshots, it's nearly impossible for me (or any other potential helper) to reconstruct your sheets for testing.

Please check my signature and upload Sample sheets using XL2BB, you'll find that members will be much more inclined to help.

 

[Lioness227]

Hi,

Looking at the formula you're using (or trying to use), I don't think it'll work, you have made arrays inside the AND statement, but it won't work as-is, even if the formula is array entered (CSE).
I can't make any suggestions at this time, as you only uploaded pics/screenshots, it's nearly impossible for me (or any other potential helper) to reconstruct your sheets for testing.

Please check my signature and upload Sample sheets using XL2BB, you'll find that members will be much more inclined to help.

Hi,

Thank you for your response. I should have uploading via XL2BB, thankfully it worked and you can see?

Untitled spreadsheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Name	Surname	Product	Jan 2020	Feb 2020	Mar 2020	Apr 2020	May 2020	Jun 2020	Jul 2020	Aug 2020	Sep 2020	Oct 2020	Nov 2020	Dec 2020
3	Lola		Product A	3	3
4	Fred		Product B	3	3
5	George		Product C	3	3
6	David		Product D	3	3
7	Liam		Product A	3	3
8	Mark		Product D	3	3
9
10
11
2020-Monthly payment

Untitled spreadsheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Surname	January 2020	February 2020	March 2020	April 2020	May 2020	June 2020	July 2020	August 2020	September 2020	October 2020	November 2020	December 2020
2	Lisa		100
3	Laurent									300
4	Jessica										400
5	Oliver													100
6	Olivia								200
7	Manuel												1000
8	Dario					5000
9	Lou								200
2020-One off payment

Untitled spreadsheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Product	ID	Jan Fund Raised	Feb Fund Raised	Mar Fund Raised	Apr Fund Raised	May Fund Raised	Jun Fund Raised	Jul Fund Raised	Aug Fund Raised	Sep Fund Raised	Oct Fund Raised	Nov Fund Raised	Dec Fund Raised
2	Product A		#VALUE!
3	Product B
4	Product C
5	Product D
6	Product E
7	Product F
8	Product G
9	Product H
10	Product I
11	Product J
12	Product K
13	Product L
14	Product M
15	Product N
16	Product O
17	Product P
18	Product Q
19	Product R
20	Product S
21	Product T
22	Product U
23	Product V
24	Product W
25	Product X
26	Product Y
27	Product Z
28	Etc.
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57	TOTAL
58
59	0.003333333	0.01
60	0.33333334	1
61	Monthly	3
62	One off:	0.01
63
2020-budget

Cell Formulas
Range	Formula
C2	C2	=IF(AND('2020-Monthly payment'!C3:C1000="Product A",'2020-Monthly payment'!D3:D1000=3),$A$60*SUM('2020-Monthly payment'!D3:D1000),$A$59*SUM('2020-Monthly payment'!D3:D1000))+($B$62*'2020-One off payment'!C2:C1000)

 

[jtakw]

Thank you for uploading samples.

In the formula you're trying to use, you have 2 different calculations.
I already have a formula for the first part; however, I'm not understanding your requirements for when and which calculation to use.

Can you please explain in detail what you expect as the answer (and why) for Budget sheet C2, and is the formula supposed to produce results across for the Months, or down for the Products?
Also, please confirm that the values in Budget sheet A59:B62 are static.

 

[Lioness227]

Hi,

thank you very much for your help. Apologies a the notification from MrExcel went to my spam.

i am looking for
- Budget raised from monthly payment per month and for each product (depending on which product customers select, and when, the result will differ, as they can start and stop at any time so we cannot allocate automatically a 12 month subscription = £36)
- Budget raised from the one off payment (can come from any existing or new customer, and whatever the amount, 1% will be allocated to each product). There are 54 products in total for now so I need to extract 54% of that amount to be allocated equally to each product, and the rest so 46% will be allocated to something else.

does that make sense?

values in budget sheet A59:B62 are static indeed.

many thanks for your help

 

[Lioness227]

Hi,

thank you very much for your help. Apologies a the notification from MrExcel went to my spam.

i am looking for
- Budget raised from monthly payment per month and for each product (depending on which product customers select, and when, the result will differ, as they can start and stop at any time so we cannot allocate automatically a 12 month subscription = £36)
- Budget raised from the one off payment (can come from any existing or new customer, and whatever the amount, 1% will be allocated to each product). There are 54 products in total for now so I need to extract 54% of that amount to be allocated equally to each product, and the rest so 46% will be allocated to something else.

does that make sense?

values in budget sheet A59:B62 are static indeed.

many thanks for your help

@jtakw hopefully you saw my response here

 

[Lioness227]

@jtakw hope you can still help

 

[jtakw]

Apologies for not getting back to you sooner, been real busy with work past couple of weeks, and still am, was only able to log in now and then for a short time.

I did see your response in Post #5, however, I'm still not clear what your desired outcome is.
As I asked, I see that you have 2 Different calculations in your formula, I'm unclear as to what condition triggers which calculation.

For the 1st part of your formula, this is what I have:

=SUMPRODUCT(('2020-Monthly payment'!$C$3:$C$1000=$A2)*('2020-Monthly payment'!D$3:D$1000=3)*$A$60)

I don't know if we're supposed to Add the second part of your formula to this result, OR, under some condition, it's the result from above, otherwise, use the second part...?
Your 2020-One off payment sheet has no product codes, so for 1/1/2020, we have 3 from various people for various products in 2020-Monthly payment, AND, 100 from 1 person not assigned to any product, I see you say 1% to each of 54 products (and where does the rest go?), but as I asked above, do you just want the Total?

It might help if you can manually include desired results for, say, 2020-Budget C2:E5

Book3.xlsx
	A	B	C	D	E
1	Product	ID	Jan Fund Raised	Feb Fund Raised	Mar Fund Raised
2	Product A	#VALUE!	0.66666668	0.66666668	0
3	Product B		0.33333334	0.33333334	0
4	Product C		0.33333334	0.33333334	0
5	Product D		0.66666668	0.66666668	0
6	Product E		0	0	0
2020-budget

Cell Formulas
Range	Formula
B2	B2	=IF(AND('2020-Monthly payment'!C3:C1000="Product A",'2020-Monthly payment'!D3:D1000=3),$A$60*SUM('2020-Monthly payment'!D3:D1000),$A$59*SUM('2020-Monthly payment'!D3:D1000))+($B$62*'2020-One off payment'!C2:C1000)
C2:E6	C2	=SUMPRODUCT(('2020-Monthly payment'!$C$3:$C$1000=$A2)*('2020-Monthly payment'!D$3:D$1000=3)*$A$60)

Your formula in B2, mine (so far) in C2:E6

 

[Lioness227]

Hi,
No worries I guess it is positive to be busy at work during this pandemic

Both sheets are supposed to be added, none is triggering the other one in the final result.
First one being monthly payments, second one being for one off payments. So ultimately we just need to add both calculations onto the final sheet.
Does that make sense?

regarding your second question for the one off payments:
Indeed, whatever the amount, 1% will be allocated to each product. There are 54 products in total so 54% will be taken, and the remaining 46% will go to another box below. I haven’t added it but indeed we should have a new row below to which we allocate 46% (relating to fees, admin costs, etc.).

Sorry it is not straight forward to explain by text but thankfully it makes sense and will confirm whether your current suggested formula is correct or if you think we should amend something?

many thanks again

 

[Lioness227]

Hi @jtakw,

assuming you are busy with work, just hoping it all makes sense. If not I can send you the file via email if possible?

Many thanks,

Elodie