Sumproduct Help

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
The below works just fine if column A was Text. In this case it is Numbers.
Test 8

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 54px"><COL style="WIDTH: 84px"><COL style="WIDTH: 64px"><COL style="WIDTH: 51px"><COL style="WIDTH: 60px"><COL style="WIDTH: 82px"><COL style="WIDTH: 108px"><COL style="WIDTH: 108px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">CODE #</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">PAY</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">DATE</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">CELL</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">JOB NO.</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">Jun</TD><TD style="BACKGROUND-COLOR: #ffff99; FONT-WEIGHT: bold">Insert the Month you need in the yellow box</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">200</TD><TD style="TEXT-ALIGN: center">1940</TD><TD style="TEXT-ALIGN: center">6/22/2010</TD><TD style="TEXT-ALIGN: center">$B$51</TD><TD style="TEXT-ALIGN: center">CR0047</TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">200</TD><TD style="TEXT-ALIGN: center">1240</TD><TD style="TEXT-ALIGN: center">6/22/2010</TD><TD style="TEXT-ALIGN: center">$B$135</TD><TD style="TEXT-ALIGN: center">CR0047</TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">300</TD><TD style="TEXT-ALIGN: center">1455</TD><TD style="TEXT-ALIGN: center">6/20/2010</TD><TD style="TEXT-ALIGN: center">$B$9</TD><TD style="TEXT-ALIGN: center">RA0045</TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">300</TD><TD style="TEXT-ALIGN: center">248</TD><TD style="TEXT-ALIGN: center">6/20/2010</TD><TD style="TEXT-ALIGN: center">$B$93</TD><TD style="TEXT-ALIGN: center">RA0045</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">300</TD><TD style="TEXT-ALIGN: center">970</TD><TD style="TEXT-ALIGN: center">7/16/2010</TD><TD style="TEXT-ALIGN: center">$B$72</TD><TD style="TEXT-ALIGN: center">RA0048</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">300</TD><TD style="TEXT-ALIGN: center">620</TD><TD style="TEXT-ALIGN: center">7/16/2010</TD><TD style="TEXT-ALIGN: center">$B$156</TD><TD style="TEXT-ALIGN: center">RA0048</TD><TD> </TD><TD style="FONT-WEIGHT: bold">CODE TOTALS</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">400</TD><TD style="TEXT-ALIGN: center">485</TD><TD style="TEXT-ALIGN: center">6/21/2010</TD><TD style="TEXT-ALIGN: center">$B$30</TD><TD style="TEXT-ALIGN: center">ME0046</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">200</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">$0.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center">400</TD><TD style="TEXT-ALIGN: center">310</TD><TD style="TEXT-ALIGN: center">6/21/2010</TD><TD style="TEXT-ALIGN: center">$B$114</TD><TD style="TEXT-ALIGN: center">ME0046</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">300</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">$0.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">400</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">$0.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">TOTAL </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">$0.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #99cc00; FONT-WEIGHT: bold">DOUBLE CHECK AREA</TD><TD style="BACKGROUND-COLOR: #99cc00"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">July</TD><TD style="BACKGROUND-COLOR: #99cc00; FONT-WEIGHT: bold">Month 2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #99cc00; FONT-SIZE: 9pt; FONT-WEIGHT: bold">TOTAL PAYROLL</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">$7,268.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #99cc00; FONT-WEIGHT: bold">Jun TOTAL</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">$0.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #99cc00; FONT-WEIGHT: bold">July TOTAL</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">$1,590.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #99cc00; FONT-WEIGHT: bold">DIFFERENCE</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-WEIGHT: bold">$5,678.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; FONT-WEIGHT: bold">IF $0.00 - OK </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99cc00; COLOR: #ff0000; FONT-WEIGHT: bold">IF NOT FIX IT</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>H8</TD><TD>=SUMPRODUCT(--($A$2:$A$21="200"),--($C$2:$C$21-DAY($C$2:$C$21)+1=("1-"&$G$1)+0),$B$2:$B$21)</TD></TR><TR><TD>H9</TD><TD>=SUMPRODUCT(--($A$2:$A$21="300"),--($C$2:$C$21-DAY($C$2:$C$21)+1=("1-"&$G$1)+0),$B$2:$B$21)</TD></TR><TR><TD>H10</TD><TD>=SUMPRODUCT(--($A$2:$A$21="400"),--($C$2:$C$21-DAY($C$2:$C$21)+1=("1-"&$G$1)+0),$B$2:$B$21)</TD></TR><TR><TD>H12</TD><TD>=SUM(H8:H10)</TD></TR><TR><TD>G16</TD><TD>=TEXT((1&G1)+31,"mmmm")</TD></TR><TR><TD>H17</TD><TD>=SUM(B:B)</TD></TR><TR><TD>G18</TD><TD>=G1&" "&"TOTAL"</TD></TR><TR><TD>H18</TD><TD>=H12</TD></TR><TR><TD>G19</TD><TD>=G16&" "&"TOTAL"</TD></TR><TR><TD>H19</TD><TD>=SUMPRODUCT(--($A$2:$A$21="200")+($A$2:$A$21="300")+($A$2:$A$21="400")--($C$2:$C$21-DAY($C$2:$C$21)+1=("1-"&$G$16)+0),$B$2:$B$21)</TD></TR><TR><TD>H20</TD><TD>=H17-SUM(H18:H19)</TD></TR><TR><TD>H21</TD><TD>=IF($H$20=0,"","IF NOT FIX IT")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4



I have tried numerous sceneriors to try and bring in the values to H8:H10 and also H19with no luck.

Anybodody have a clue on this?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
Maybe this will help in my explanation of my problem:

Excel Workbook
ABCDEFGH
1CODE #PAYDATECELLJOB NO.JunInsert the Month you need in the yellow box
2Me19406/22/2010$B$51CR0047
3Me12406/22/2010$B$135CR0047
430014556/20/2010$B$9RA0045
53002486/20/2010$B$93RA0045
63009707/16/2010$B$72RA0048
73006207/16/2010$B$156RA0048CODE TOTALS
84004856/21/2010$B$30ME0046200$3,180.00
Test 8


I changed A2 & A3 to Me. I then changed the formua in H8 to refelect "me". This formula brings in the right Amounts from Col B.

So, my question is How do I do the same thing if column A is a Number??
 

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
Bump

Sorry if I seem a little impacient.

This is, I'm hoping the last part of my project, if I can get the Foumulas to work.

Where is ALadin or Dominic when you need them.

But then again Help from anybody is welcome. :)
 

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
The Obvious

Thanks for opening my eyes. I think I can deal with it now,
 

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
Well I thought I had it solved. :(

A little different lay out presented below. My problem is with what is in Cell G15. It seems to be adding the total of column C plus the total of July's total.

Excel Workbook
ABCDEFG
1CODE #PAY CODEPAYDATEJUN
22002503019406/22/2010
32002505012406/22/2010CODE TOTALS
43002503014556/20/2010CODE 200$3,180.00
5300250502486/20/2010CODE 300$1,703.00
6300250309707/16/2010CODE 400$795.00
7300250506207/16/2010CODE 500
8400250304856/21/2010TOTAL$5,678.00
9400250503106/21/2010
10
11DOUBLE CHECK AREA
12JulMonth 2
13TOTAL PAYROLL$7,268.00
14JUN TOTAL$5,678.00
15Jul TOTAL$8,858.00
16DIFFERENCE-$7,268.00
17IF $0.00 - OK IF NOT FIX IT
TEST


I believe the problem is how I have the formula in G15. All other formulas are bringing in the correct values.

Could somebody take a look for me please.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You have a comma missing (and you don't need -- at the start), should be

=SUMPRODUCT(($A$2:$A$21=200)+($A$2:$A$21=300)+($A$2:$A$21=400),--($D$2:$D$21-DAY($D$2:$D$21)+1=("1-"&$F$12)+0),$C$2:$C$21)
 

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
Thanks Barry

I thought it might be something a little simple that I was missing.

"Leave it to the Pro's on Formulas" to spot it. ;)

Thanks again. :)
 

Forum statistics

Threads
1,171,065
Messages
5,873,585
Members
432,985
Latest member
leahw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top