SUMPRODUCT help

[JamesonMH]

Hi there,

I've exhausted my options and really need some help with this.

I'm dealing with a fixed payroll table where the column headers duplicate across (but the data is different). Given a bi-weekly income, I want to calculate what the CPP amount is. For example, if income was $2,011.45, then CPP should be $92.90 since it falls between $2,011.28-2,011.47. Obviously if it were just the 3 columns it would be easy, but that's not how the payroll table is.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Bi-weekly income[/TD]
[TD][/TD]
[TD][/TD]
[TD]Bi-weekly income[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]From[/TD]
[TD]To[/TD]
[TD]CPP[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]CPP[/TD]
[/TR]
[TR]
[TD]$1,996.33[/TD]
[TD]$1,996.52[/TD]
[TD]$92.16[/TD]
[TD]$2,010.88[/TD]
[TD]$2,011.07[/TD]
[TD]$92.88[/TD]
[/TR]
[TR]
[TD]1,996.53[/TD]
[TD]1,996.73[/TD]
[TD]92.17[/TD]
[TD]2,011.08[/TD]
[TD]2,011.27[/TD]
[TD]92.89[/TD]
[/TR]
[TR]
[TD]1,996.74[/TD]
[TD]1,996.93[/TD]
[TD]92.18[/TD]
[TD]2,011.28[/TD]
[TD]2,011.47[/TD]
[TD]92.90[/TD]
[/TR]
[TR]
[TD]1,996.94[/TD]
[TD]1,997.13[/TD]
[TD]92.19[/TD]
[TD]2,011.48[/TD]
[TD]2,011.68[/TD]
[TD]92.91[/TD]
[/TR]
</tbody>[/TABLE]

I've tried some INDEX/SUMPRODUCT/MIN/IF/ROW arrays but no luck so far.

Please help!

James

 

[James006]

Hi James,

Is there something preventing you from transposing all your existing Columns ... into the 3 columns you need ... ?

Another worksheet could handle this issue for you ... :wink:

 

[JamesonMH]

Hi James,

Is there something preventing you from transposing all your existing Columns ... into the 3 columns you need ... ?

Another worksheet could handle this issue for you ... :wink:

I agree with that. Thing is there's many pages of this source data (payroll table) with more columns than in my example...so I'm hoping to have a cleaner/single cell solution that leaves source data in check, finds the correct income range and just returns the CPP rate.

Plus I won't be the only one using so training others how to tranpose/manipulate (even though pretty simple) source data just raises risk of errors.

 

[James006]

May I ask you How Many Columns in total do you have in your Payroll table ...???

 

[Aladin Akyurek]

Let A1:F5 house the data with headers in A1:F1.

A8: $2,011.45

In B8 control+shift+enter, not just enter:

=MIN(IF(MOD(COLUMN($A$2:$E$5)-COLUMN($A$2:$A$5),3)=0,IF(A8>=$A$2:$E$5,IF(A8<=$B$2:$F$5,COLUMN($A$2:$E$5)))))

In C8 control+shift+enter, not just enter:

=VLOOKUP(A8,CHOOSE({1,2},INDEX($A$2:$F$5,0,B8),INDEX($A$2:$F$5,0,B8+2)),2,1)

 

[JamesonMH]

May I ask you How Many Columns in total do you have in your Payroll table ...???

For sure...it's 12 columns (double my example) by 35 pages...

 

[Nishant94]

Assuming you have lookup table like this:

Book1
	A	B	C	D	E	F	G	H	I
1	From	To	CPP	From	To	CPP	From	To	CPP
2	1996.33	1996.52	92.16	2010.88	2011.07	92.88	2011.69	2011.88	92.92
3	1996.53	1996.73	92.17	2011.08	2011.27	92.89	2011.89	2012.08	92.93
4	1996.74	1996.93	92.18	2011.28	2011.47	92.90	2012.09	2012.28	92.94
5	1996.94	1997.13	92.19	2011.48	2011.68	92.91	2012.29	2012.48	92.95
Sheet1

Then you can use this:

Book1
	A	B
7	Income	CPP
8	2011.45	92.90
9	1996.85	92.18
10	2011.95	92.93
11	2011.15	92.89
Sheet1

Cell Formulas
Range	Formula
B8	{=A8-MAX(ABS(IF((A8>=$A$2:$I$5)*(A8<=$B$2:$J$5),$C$2:$K$5,A8)-A8))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Nishant94]

Another Option:

Data Table

Book1
	A	B	C	D	E	F	G	H	I
1	From	To	CPP	From	To	CPP	From	To	CPP
2	1996.33	1996.52	92.16	2010.88	2011.07	92.88	2011.69	2011.88	92.92
3	1996.53	1996.73	92.17	2011.08	2011.27	92.89	2011.89	2012.08	92.93
4	1996.74	1996.93	92.18	2011.28	2011.47	92.90	2012.09	2012.28	92.94
5	1996.94	1997.13	92.19	2011.48	2011.68	92.91	2012.29	2012.48	92.95
Sheet1

Result:

Book1
	A	B
7	Income	CPP
8	2011.45	92.90
9	1996.85	92.18
10	2012.19	92.94
11	2011.15	92.89
Sheet1

Cell Formulas
Range	Formula
B8	=AGGREGATE(14,,((A8>=$A$2:$I$5)*(A8<=$B$2:$J$5)*MOD(COLUMN($A$2:$I$5)*(ROW($A$2:$A$5)^0),3)=1)*$C$2:$K$5,1)

 

[James006]

For sure...it's 12 columns (double my example) by 35 pages...

Well,

With 35 individual worksheets showing 12 Columns each ... you are talking about an extremely sophisticated ' Payroll ' system ...!!!

For sure, it will be much safer to build a reference database ...!!!

 

[JamesonMH]

Assuming you have lookup table like this:

	A	B	C	D	E	F	G	H	I

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: center"]From[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: center"]To[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: center"]CPP[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: center"]From[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: center"]To[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: center"]CPP[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: center"]From[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: center"]To[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: center"]CPP[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1996.33[/TD]
[TD="align: right"]1996.52[/TD]
[TD="align: right"]92.16[/TD]
[TD="align: right"]2010.88[/TD]
[TD="align: right"]2011.07[/TD]
[TD="align: right"]92.88[/TD]
[TD="align: right"]2011.69[/TD]
[TD="align: right"]2011.88[/TD]
[TD="align: right"]92.92[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1996.53[/TD]
[TD="align: right"]1996.73[/TD]
[TD="align: right"]92.17[/TD]
[TD="align: right"]2011.08[/TD]
[TD="align: right"]2011.27[/TD]
[TD="align: right"]92.89[/TD]
[TD="align: right"]2011.89[/TD]
[TD="align: right"]2012.08[/TD]
[TD="align: right"]92.93[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1996.74[/TD]
[TD="align: right"]1996.93[/TD]
[TD="align: right"]92.18[/TD]
[TD="align: right"]2011.28[/TD]
[TD="align: right"]2011.47[/TD]
[TD="align: right"]92.90[/TD]
[TD="align: right"]2012.09[/TD]
[TD="align: right"]2012.28[/TD]
[TD="align: right"]92.94[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1996.94[/TD]
[TD="align: right"]1997.13[/TD]
[TD="align: right"]92.19[/TD]
[TD="align: right"]2011.48[/TD]
[TD="align: right"]2011.68[/TD]
[TD="align: right"]92.91[/TD]
[TD="align: right"]2012.29[/TD]
[TD="align: right"]2012.48[/TD]
[TD="align: right"]92.95[/TD]

</tbody>

Sheet1​

Then you can use this:

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: center"]Income[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: center"]CPP[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2011.45[/TD]
[TD="align: right"]92.90[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1996.85[/TD]
[TD="align: right"]92.18[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2011.95[/TD]
[TD="align: right"]92.93[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2011.15[/TD]
[TD="align: right"]92.89[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B8[/TH]
[TD="align: left"]{=A8-MAX(ABS(IF((A8>=$A$2:$I$5)*(A8<=$B$2:$J$5),$C$2:$K$5,A8)-A8))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Brilliant! This is much cleaner than what I was attempting. Much appreciated Nishant! I'll take a look at your AGGREGATE solution as well. Thank you!