Sumproduct Help

N

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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
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??
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
418
Micron
M
P
  • Solved
Save as text file in a certain format similar to an xml
2
Replies
16
Views
226
rlv01
R
C
  • Question
Data Entry and Report Generation
Replies
2
Views
133
Carla carla
C
B
  • Question
Search partial text in a lookup
Replies
1
Views
145
felixstraube
F
A
  • Solved
Create matrix of combinations from list of values/attributes
Replies
3
Views
522
ArsenioV4
A

Forum statistics

Threads
1,214,411
Messages
6,119,360
Members
448,888
Latest member
Arle8907

Share this page

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
Back
Top