Providing total revenue based on account and probability

[Peakey123]

Hi all,

I have a table based like the following in a sheet called "Pipeline". It shows difference projects within certain accounts, their chance of success, and projected revenue should they go ahead:

Account	Project	Chance of Win	Dec-21	Jan-22	Feb-22	Mar-22
Alpha	Project A	30%	10	20	20	20
Beta	Project B	10%	100	100	50	50
Omega	Project C	70%	60	70	80	90
Alpha	Project D	60%	50	50	50	50

In another sheet called "Alpha", i have the following table:

£k	Dec-21	Jan-22	Feb-22	Mar-22
FY22 Pipeline

Is there a way for me to be able to automatically input the estimated FY22 pipeline revenue for all Alpha account projects for December into the Dec-21 area of the above table. This needs to be estimated revenue so i need to times the revenue for Dec-21 by the chance of success before adding to the summation. So for Project A this would be 10*0.3=3.

Any help would be greatly appreciated.

Thanks in advance

 

[Toadstool]

Hi Peakey123,

Does this do what you want?

Peakey123.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Account	Project	Chance of Win	21-Dec	22-Jan	22-Feb	22-Mar			£k	21-Dec	22-Jan	22-Feb	22-Mar
2	Alpha	Project A	30%	10	20	20	20			FY22 Pipeline	33	36	36	36
3	Beta	Project B	10%	100	100	50	50
4	Omega	Project C	70%	60	70	80	90
5	Alpha	Project D	60%	50	50	50	50
6
Sheet1

Cell Formulas
Range	Formula
K2:N2	K2	=SUMPRODUCT(($C$2:$C$999*$D2:$G999)*($D$1:$G$1=K$1)*($A$2:$A$999="Alpha"))