Sumproduct Second Array to get dynamic

[imran1059]

Hi there,

I have below listed three table starting from Cell A1 (which contains text "Estimated Sales"). last table is my required table. 1st table contains estimated sales, second table contains estimated completion % and third table contains sumproduct formula which is giving me the accumulated monthly completion. Currently sumproduct formula is working fine but is not dynamic.

Estimated Sales​
Divisions​	Regions​												Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11	Month 12
AED​	Eastern​												3,000	5,000	4,000	3,000	5,000	8,000	3,256	3,000	5,879	8,000	1,589	6,589
AED​	Central​												2,000	6,000	3,000	158	5,000	7,513	5,893	2,598	5,000	5,897	6,987	1,570
AED​	Western​												5,879	3,654	2,000	3,699	5,000	5,236	3,289	3,000	5,879	8,000	5,000	1,480
TCD​	Eastern​												2,543	2,589	1,000	2,589	1,000	7,812	1,587	2,000	1,000	5,897	1,325	4,589
TCD​	Central​												2,578	2,589	2,000	4,789	1,000	2,589	1,000	4,897	4,589	3,000	1,000	2,000
TCD​	Western​												1,000	2,589	5,478	3,258	1,000	2,589	4,893	2,000	1,000	5,698	1,564	5,897
BRD​	Eastern​												7,588	8,000	8,000	235	8,000	3,263	8,000	6,328	8,000	8,000	8,000	1,589
BRD​	Central​												6,000	8,000	489	9,876	4,587	2,489	3,596	2,540	6,397	4,897	7,894	1,698
BRD​	Western​												9,000	8,000	4,789	7,584	489	7,456	8,000	5,896	4,589	8,713	8,596	8,000
Estmiated completion %​
Divisions​													Month 12	Month 11	Month 10	Month 9	Month 8	Month 7	Month 6	Month 5	Month 4	Month 3	Month 2	Month 1
AED​													5%	0%	8%	0%	4%	0%	8%	5%	10%	0%	5%	5%
TCD​													10%	6%	15%	6%	10%	5%	9%	5%	10%	5%	10%	10%
BRD​													5%	0%	12%	5%	5%	0%	5%	5%	10%	5%	5%	5%
Esimated Completion ( Required )​
Divisions​													Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11	Month 12
AED​	Eastern​												150​	400​	450​	650​	1,050​	1,540​	1,463​	1,400​	1,929​	2,214​	2,097​	2,070​
AED​	Central​												100​	400​	450​	358​	958​	1,386​	1,316​	1,250​	1,628​	2,186​	2,283​	2,063​
AED​	Western​												294​	477​	283​	873​	1,094​	1,365​	1,188​	1,389​	1,656​	2,230​	1,968​	1,973​
TCD​	Eastern​												254​	513​	486​	743​	795​	1,469​	1,659​	1,453​	1,905​	2,195​	2,664​	2,435​
TCD​	Central​												258​	517​	588​	1,066​	1,067​	1,160​	1,350​	1,626​	2,252​	2,305​	2,594​	2,474​
TCD​	Western​												100​	359​	857​	1,103​	1,009​	1,289​	1,681​	1,804​	1,740​	2,495​	2,607​	3,173​
BRD​	Eastern​												379​	779​	1,179​	1,571​	1,991​	2,154​	1,787​	2,471​	2,634​	4,160​	3,736​	4,115​
BRD​	Central​												300​	700​	724​	1,518​	1,848​	1,597​	1,946​	1,708​	2,299​	2,526​	2,964​	2,751​
BRD​	Western​												450​	850​	1,089​	1,919​	1,893​	2,105​	2,195​	2,185​	2,924​	3,841​	4,004​	3,828​

Formulas in last table are as below. In this fomula I need second array to be dynamic based on Division which is in column A. If Division is changed from AED to TCD then the row number in second array should be change

Esimated Completion ( Required )​

Divisions​

Month 1

Month 2

Month 3

Month 4

Month 5

Month 6

Month 7

Month 8

Month 9

Month 10

Month 11

Month 12

AED​

Eastern​

=SUMPRODUCT(C3:N3,$N15:$Y15)​

=SUMPRODUCT(D3:O3,$N15:$Y15)​

=SUMPRODUCT(E3:P3,$N15:$Y15)​

=SUMPRODUCT(F3:Q3,$N15:$Y15)​

=SUMPRODUCT(G3:R3,$N15:$Y15)​

=SUMPRODUCT(H3:S3,$N15:$Y15)​

=SUMPRODUCT(I3:T3,$N15:$Y15)​

=SUMPRODUCT(J3:U3,$N15:$Y15)​

=SUMPRODUCT(K3:V3,$N15:$Y15)​

=SUMPRODUCT(L3:W3,$N15:$Y15)​

=SUMPRODUCT(M3:X3,$N15:$Y15)​

=SUMPRODUCT(N3:Y3,$N15:$Y15)​

AED​

Central​

=SUMPRODUCT(C4:N4,$N15:$Y15)​

=SUMPRODUCT(D4:O4,$N15:$Y15)​

=SUMPRODUCT(E4:P4,$N15:$Y15)​

=SUMPRODUCT(F4:Q4,$N15:$Y15)​

=SUMPRODUCT(G4:R4,$N15:$Y15)​

=SUMPRODUCT(H4:S4,$N15:$Y15)​

=SUMPRODUCT(I4:T4,$N15:$Y15)​

=SUMPRODUCT(J4:U4,$N15:$Y15)​

=SUMPRODUCT(K4:V4,$N15:$Y15)​

=SUMPRODUCT(L4:W4,$N15:$Y15)​

=SUMPRODUCT(M4:X4,$N15:$Y15)​

=SUMPRODUCT(N4:Y4,$N15:$Y15)​

AED​

Western​

=SUMPRODUCT(C5:N5,$N15:$Y15)​

=SUMPRODUCT(D5:O5,$N15:$Y15)​

=SUMPRODUCT(E5:P5,$N15:$Y15)​

=SUMPRODUCT(F5:Q5,$N15:$Y15)​

=SUMPRODUCT(G5:R5,$N15:$Y15)​

=SUMPRODUCT(H5:S5,$N15:$Y15)​

=SUMPRODUCT(I5:T5,$N15:$Y15)​

=SUMPRODUCT(J5:U5,$N15:$Y15)​

=SUMPRODUCT(K5:V5,$N15:$Y15)​

=SUMPRODUCT(L5:W5,$N15:$Y15)​

=SUMPRODUCT(M5:X5,$N15:$Y15)​

=SUMPRODUCT(N5:Y5,$N15:$Y15)​

TCD​

Eastern​

=SUMPRODUCT(C6:N6,$N16:$Y16)​

=SUMPRODUCT(D6:O6,$N16:$Y16)​

=SUMPRODUCT(E6:P6,$N16:$Y16)​

=SUMPRODUCT(F6:Q6,$N16:$Y16)​

=SUMPRODUCT(G6:R6,$N16:$Y16)​

=SUMPRODUCT(H6:S6,$N16:$Y16)​

=SUMPRODUCT(I6:T6,$N16:$Y16)​

=SUMPRODUCT(J6:U6,$N16:$Y16)​

=SUMPRODUCT(K6:V6,$N16:$Y16)​

=SUMPRODUCT(L6:W6,$N16:$Y16)​

=SUMPRODUCT(M6:X6,$N16:$Y16)​

=SUMPRODUCT(N6:Y6,$N16:$Y16)​

TCD​

Central​

=SUMPRODUCT(C7:N7,$N16:$Y16)​

=SUMPRODUCT(D7:O7,$N16:$Y16)​

=SUMPRODUCT(E7:P7,$N16:$Y16)​

=SUMPRODUCT(F7:Q7,$N16:$Y16)​

=SUMPRODUCT(G7:R7,$N16:$Y16)​

=SUMPRODUCT(H7:S7,$N16:$Y16)​

=SUMPRODUCT(I7:T7,$N16:$Y16)​

=SUMPRODUCT(J7:U7,$N16:$Y16)​

=SUMPRODUCT(K7:V7,$N16:$Y16)​

=SUMPRODUCT(L7:W7,$N16:$Y16)​

=SUMPRODUCT(M7:X7,$N16:$Y16)​

=SUMPRODUCT(N7:Y7,$N16:$Y16)​

TCD​

Western​

=SUMPRODUCT(C8:N8,$N16:$Y16)​

=SUMPRODUCT(D8:O8,$N16:$Y16)​

=SUMPRODUCT(E8:P8,$N16:$Y16)​

=SUMPRODUCT(F8:Q8,$N16:$Y16)​

=SUMPRODUCT(G8:R8,$N16:$Y16)​

=SUMPRODUCT(H8:S8,$N16:$Y16)​

=SUMPRODUCT(I8:T8,$N16:$Y16)​

=SUMPRODUCT(J8:U8,$N16:$Y16)​

=SUMPRODUCT(K8:V8,$N16:$Y16)​

=SUMPRODUCT(L8:W8,$N16:$Y16)​

=SUMPRODUCT(M8:X8,$N16:$Y16)​

=SUMPRODUCT(N8:Y8,$N16:$Y16)​

BRD​

Eastern​

=SUMPRODUCT(C9:N9,$N17:$Y17)​

=SUMPRODUCT(D9:O9,$N17:$Y17)​

=SUMPRODUCT(E9:P9,$N17:$Y17)​

=SUMPRODUCT(F9:Q9,$N17:$Y17)​

=SUMPRODUCT(G9:R9,$N17:$Y17)​

=SUMPRODUCT(H9:S9,$N17:$Y17)​

=SUMPRODUCT(I9:T9,$N17:$Y17)​

=SUMPRODUCT(J9:U9,$N17:$Y17)​

=SUMPRODUCT(K9:V9,$N17:$Y17)​

=SUMPRODUCT(L9:W9,$N17:$Y17)​

=SUMPRODUCT(M9:X9,$N17:$Y17)​

=SUMPRODUCT(N9:Y9,$N17:$Y17)​

BRD​

Central​

=SUMPRODUCT(C10:N10,$N17:$Y17)​

=SUMPRODUCT(D10:O10,$N17:$Y17)​

=SUMPRODUCT(E10:P10,$N17:$Y17)​

=SUMPRODUCT(F10:Q10,$N17:$Y17)​

=SUMPRODUCT(G10:R10,$N17:$Y17)​

=SUMPRODUCT(H10:S10,$N17:$Y17)​

=SUMPRODUCT(I10:T10,$N17:$Y17)​

=SUMPRODUCT(J10:U10,$N17:$Y17)​

=SUMPRODUCT(K10:V10,$N17:$Y17)​

=SUMPRODUCT(L10:W10,$N17:$Y17)​

=SUMPRODUCT(M10:X10,$N17:$Y17)​

=SUMPRODUCT(N10:Y10,$N17:$Y17)​

BRD​

Western​

=SUMPRODUCT(C11:N11,$N17:$Y17)​

=SUMPRODUCT(D11:O11,$N17:$Y17)​

=SUMPRODUCT(E11:P11,$N17:$Y17)​

=SUMPRODUCT(F11:Q11,$N17:$Y17)​

=SUMPRODUCT(G11:R11,$N17:$Y17)​

=SUMPRODUCT(H11:S11,$N17:$Y17)​

=SUMPRODUCT(I11:T11,$N17:$Y17)​

=SUMPRODUCT(J11:U11,$N17:$Y17)​

=SUMPRODUCT(K11:V11,$N17:$Y17)​

=SUMPRODUCT(L11:W11,$N17:$Y17)​

=SUMPRODUCT(M11:X11,$N17:$Y17)​

=SUMPRODUCT(N11:Y11,$N17:$Y17)​

I do hope, I am clear to my question. If there is any other idea to get the same information with or without helper columns then please share.


Regards,
Imran.

 

[BarryL]

Hello, how are the final table numbers being arrived at?

For example BRD Western Month 4 - how is 1919 calculated?

 

[imran1059]

Hello, how are the final table numbers being arrived at?

For example BRD Western Month 4 - how is 1919 calculated?

Here is the formula for BRD Western Month 4

 

[BarryL]

So you're multiplying Month 1's value by Month 4s percentage, Month 2's value by month 3's percentage etc.

Is that the expectation to multiply a percentage for different months then the months value?

workings below:

9000 * 0.1=	8000 * 0.05=	4789 * 0.05=	7584 * 0.05=
900	400	239	379
900+400+239.45+379.2
1,919

 

[imran1059]

So you're multiplying Month 1's value by Month 4s percentage, Month 2's value by month 3's percentage etc.

Is that the expectation to multiply a percentage for different months then the months value?

workings below:

9000 * 0.1=	8000 * 0.05=	4789 * 0.05=	7584 * 0.05=
900	400	239	379
900+400+239.45+379.2
1,919

Exactly ..... by the way in first and last table I should have named the months themselves. I mean in the first table which is estimated sales, the column headings should have been Jan Feb Mar Apr and so on till Dec. Same should have been the case with the accumulated completion table which is required. Monthly expected completion % from sales is listed in 2nd table. I didnt name them Jan Feb Mar to avoid confusion because expected completion is not just limited to 12 months, it could be 24 months or even more. What I am concerned is that expected completion in a single year that spans 12 months.

 

[BarryL]

Yea i was afraid of that, so when completing the month 4 calculation, you are calculating month 1 different then how you calculated it when doing the actual month 1 calculation.

For month 4, month 1's value is 900, whereas in month 1 its 450.

Hard to make that dynamic for me, hopefully someone smarter will come along and solve it.

 

[imran1059]

Yea i was afraid of that, so when completing the month 4 calculation, you are calculating month 1 different then how you calculated it when doing the actual month 1 calculation.

For month 4, month 1's value is 900, whereas in month 1 its 450.

Hard to make that dynamic for me, hopefully someone smarter will come along and solve it.

I think you didnt get it. sumproduct formula is consistent. sumproduct takes care for the variations that you mentioned in monthly calculations. All I need is to switch row number in second array if division changes. Like when I copy the formula from Yellow highlighted cell (from below sreen shot) to green highlighted cell then the formula should change from:

this
=SUMPRODUCT(F8:Q8,$N16:$Y16)

to this
=SUMPRODUCT(F9:Q9,$N17:$Y17)

because the value is column A is changed.


I dont think its complicated. It should be simple to do that. I am just not figuring it out how to do that.