Formula to calculate results based on various criteria

[brunette]

Dear all,

I have a massive sheet where all our clientes products are registered and how many licenses a cliente has.

I need to calculate the total price per client, based on what products they are subscribed to and the quantity of licenses.

So what do I need to do:

1) Establish which products they subscribe to (B - D)
2) Establish each subscribed product's unit price (G - H)
3) Add all the subscribed products' unit prices and multipy the result by the amount of licenses (E)

I think my brain is failing me because I just can't figure out how to do this...

Simplified exemple:

A1=Client; B1=Prod1; C1=Prod 2; D1=Prod 3; E1= Quantity; F1= ProdTotalPrice; G1=ProdName; H1=ProdUnitPrice
A2=Client1; B2=Yes; C2=No; D2=Yes; E2=200; F2=[formula goes here]; G2=Prod1; H2=0.50€
A3=Client 2; B3=No; C3=Yes; D3=Yes; E3=300; F3=[formula goes here]; G3=Prod 2; H3=0.60€
A4=Client 3; B4=Yes; C4=Yes; D4=Yes; E4=150; F4=[formula goes here]; G4=Prod 3; H3=0.23€

Thanks in advance for your help.

 

[Gerald Higgins]

If you have 3 products (B2, C2, D2), how does the quantity relate to that ?
Does it mean that client1 has 200 of B1 AND 200 of D1 ?
Same question for Product Unit Price in H2.

 

[brunette]

Hi Gerald,

Both product price (H1) and quantity (E) are fixed. The only thing that varies is what product each client may or may not have.

So, yes, cliente 1 has 200 of B1 and 200 of D1.

Thanks so much.

 

[FormR]

So, yes, cliente 1 has 200 of B1 and 200 of D1.

Hi, is this the result you are looking for?

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Client	Prod1	Prod 2	Prod 3	Quantity	ProdTotalPrice	ProdName	ProdUnitPrice
2	Client1	Yes	No	Yes	200	200.00 €	Prod1	0.50 €
3	Client 2	No	Yes	Yes	300	360.00 €	Prod 2	0.60 €
4	Client 3	Yes	Yes	Yes	150	103.50 €	Prod 3	0.23 €
routes

Cell Formulas
Range	Formula
F2	=COUNTIFS(B2:D2,"Yes")*E2*H2

 

[brunette]

Hi FormR

Thanks for your help.

However, it's not quite what I need, because your formula is only calculating the unit price for Prod 1 (H). If you notice, the product unit price for Prod 3 is diferent. I need to first establish all the products the client has, second add the diferente (corresponding) unit prices and then multiply by the quantity (in reality they are licenses).

Since this is diferent in 99.9% of the times, I need a formula that can calculate these steps separately, meeting the specific condition in each line, and then multiplying by the number of licenses (E).

I am not in the office today so I can't paste my Excel columns here, sadly, so I hope I am making myself understood...

=COUNTIFS(B2:D2,"Yes")*(H2+H4)*E2) [Noe: this is specific to this case only. The formula would need to take into account any possible product combination].

Thanks again!

 

[FormR]

I hope I am making myself understood...

Hi, yes - I think so, are these the expected results?

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Client	Prod1	Prod 2	Prod 3	Quantity	ProdTotalPrice	ProdName	ProdUnitPrice
2	Client1	Yes	No	Yes	200	146	Prod1	0.50 €
3	Client 2	No	Yes	Yes	300	249	Prod 2	0.60 €
4	Client 3	Yes	Yes	Yes	150	199.5	Prod 3	0.23 €
Sheet1

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(E2*$H$2:$H$4*(B2:D2="Yes")*($B$1:$D$1=$G$2:$G$4))

 

[brunette]

Hi again,

That's it! It works. Thanks so much!

ps. wow...my oooooold and trusted friend SUMPRODUCT...who would think huh?