Sumproduct not working

[rahildhody]

Hi,

I'm trying to calculate the headcount & salary by allocation for a 5yr period where the salaries change every year, but the headcount allocation stays the same. I think im doing something very silly as the sumproduct formula that im using doesnt seem to work.

Frmula used in F20 calculates correctly based on End of Month dates & calculates the count correctly as the period increases.

The same cannot be said when im trying to calculate the salary cost for the same period:

I'm sure its something really silly between the arrays but im not able to figure out what it is. Could someone please assist.
This is the fomula im trying to use in F28:

=SUMPRODUCT($N$5:$T$14*($D$5:$D$14=$D28)*($N$4:$T$4=$E28)*($F$5:$F$14<=F$18)*$G$5:$L$14*($G$4:$L$4=F$17))


Thanks in advance.

Cheers,
Rahil

 

[awoohaw]

Would you be kind enough to post the data from A4:U16 in a table so the forum can copy it?

 

[rahildhody]

Cost Centre	Products	EoM Date	2,024	2,025	2,026	2,027	2,028	2,029		Product 1	Product 2	Product 3	Product 4	Product 5	Product 6	Product 7	Total
Admin & Processing	Product 4	29-Feb-24	24,000	25,200	26,460	27,783	29,172	30,631		--	--	--	100%	--	--	--	100%
Admin & Processing	Product 4	31-Jul-23	24,500	25,725	27,011	28,362	29,780	31,269		--	--	--	100%	--	--	--	100%
Admin & Processing	Product 4	30-Jun-23	24,500	25,725	27,011	28,362	29,780	31,269		--	--	--	100%	--	--	--	100%
Admin & Processing	Product 4	31-Mar-24	24,000	25,200	26,460	27,783	29,172	30,631		--	--	--	100%	--	--	--	100%
Admin & Processing	Product 4	30-Jun-23	60,000	63,000	66,150	69,458	72,930	76,577		--	--	50%	25%	25%	--	--	100%
Admin & Processing	Product 3	31-Mar-24	24,000	25,200	26,460	27,783	29,172	30,631		--	--	100%	--	--	--	--	100%
Admin & Processing	Product 3	30-Apr-24	35,000	36,750	38,588	40,517	42,543	44,670		--	--	100%	--	--	--	--	100%
Admin & Processing	Product 3	31-May-24	35,000	36,750	38,588	40,517	42,543	44,670		--	--	100%	--	--	--	--	100%
Admin & Processing	Product 3	31-Jul-22	24,500	25,725	27,011	28,362	29,780	31,269		--	--	100%	--	--	--	--	100%
Admin & Processing	Product 5	31-May-24	29,000	30,450	31,973	33,571	35,250	37,012		--	--	--	--	100%	--	--	100%

 

[rahildhody]

answer im looking for in:

F28 = (100%*24500 + 50%*60000) / 12 = 4541

G28 = (100%*24500 + 50%*60000 + 100%*24000)/12 = 6541 and so forth for 2024

L28 should be 2025 salaries

 

[awoohaw]

How do you get to 1.5% in F20? There is only one row with Product 3 and a date less than 2024/2/29, and Product 3 column has 100% in that row.

 

[rahildhody]

How do you get to 1.5% in F20? There is only one row with Product 3 and a date less than 2024/2/29, and Product 3 column has 100% in that row.

i understand thats a little misleading. Row 9: Product 4 is split 50% to Product 3. so total for Product 3 is 100% of row 13: Product 3 & 50% of Row 9: Product 4.

thats why in my sumproduct, i dont reference column E: Products as a match, instead use N4:T4 as my product differentiator.

hopefully that makes sense?

 

[awoohaw]

i understand thats a little misleading. Row 9: Product 4 is split 50% to Product 3. so total for Product 3 is 100% of row 13: Product 3 & 50% of Row 9: Product 4.

thats why in my sumproduct, i dont reference column E: Products as a match, instead use N4:T4 as my product differentiator.

hopefully that makes sense?

it makes sense that I used the wrong range for looking up, but I still do not get 1.5%. How about posting the formula in F20, your image is nearly illegible?

I know you're looking for salary calculations, but I would like to get started with a formula based on what works.

 

[rahildhody]

it makes sense that I used the wrong range for looking up, but I still do not get 1.5%. How about posting the formula in F20, your image is nearly illegible?

I know you're looking for salary calculations, but I would like to get started with a formula based on what works.

Formula in F20:
=SUMPRODUCT($N$5:$T$14*($D$5:$D$14=$D28)*($N$4:$T$4=$E28)*($F$5:$F$14<=F$18)

Formula in F28:
=SUMPRODUCT($N$5:$T$14*($D$5:$D$14=$D28)*($N$4:$T$4=$E28)*($F$5:$F$14<=F$18)*$G$5:$L$14*($G$4:$L$4=F$17))

 

[rahildhody]

@awoohaw
any luck with the formula mate?

 

[RoryA]

You're multiplying a 6 column array by a 7 column array, which is what causes the error.

Please update your profile to indicate which version of Office you are using as it will affect what options there are available to you to solve your problems.