SUMPRODUCT Problem

Alex O

Alex O

Active Member
Joined
Mar 16, 2009
Messages
345
Office Version
  1. 365
Platform
  1. Windows
Can anyone help me figure out what the problem with my formula in J10 is? Also, can the formula be simplified any?

Thanks

<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: 93px"><COL style="WIDTH: 24px"><COL style="WIDTH: 81px"><COL style="WIDTH: 123px"><COL style="WIDTH: 12px"><COL style="WIDTH: 123px"><COL style="WIDTH: 12px"><COL style="WIDTH: 123px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">3,875,675.12</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">3,830,501.24</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">45,173.88</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; COLOR: #333399; FONT-SIZE: 8pt">BD</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; COLOR: #333399; FONT-SIZE: 8pt">PD</TD><TD style="FONT-FAMILY: Calibri; COLOR: #ffffff; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; COLOR: #333399; FONT-SIZE: 8pt">399</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 8pt"> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; COLOR: #333399; FONT-SIZE: 8pt">501</TD></TR><TR style="HEIGHT: 41px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 9pt">FL-BOYNTON</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Regular</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Disconnect New Placements</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Disconnect Closures</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Disconnect Recovery</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">267,607</TD><TD style="BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> 208,314 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> 59,293 </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> (2,907)</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #00ffff; FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> - </TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #333399; FONT-FAMILY: Calibri; COLOR: #ffffff; FONT-WEIGHT: bold">267,607</TD><TD style="BACKGROUND-COLOR: #333399; FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold"> 208,314 </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold"> 59,293 </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold"> (2,906.72)</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold"> - </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>C4</TD><TD>=SUM('YB_Monthly Placement File'!E14:E2999)</TD></TR><TR><TD>C5</TD><TD>=SUMPRODUCT(('YB_Monthly Placement File'!$I$14:$I$2292>=1)*('YB_Monthly Placement File'!$I$14:$I$2292<=399)*('YB_Monthly Placement File'!$E$14:$E$2292))</TD></TR><TR><TD>C6</TD><TD>=SUMPRODUCT(('YB_Monthly Placement File'!$I$14:$I$2292>=400)*('YB_Monthly Placement File'!$I$14:$I$2292<=699)*('YB_Monthly Placement File'!$E$14:$E$2292))</TD></TR><TR><TD>C10</TD><TD>=IF(SUMIF('YB_Monthly Placement File'!$AA$14:$AA$3000,'Number Crunch'!B10,'YB_Monthly Placement File'!$E$14:$E$3000)="","",SUMIF('YB_Monthly Placement File'!$AA$14:$AA$3000,'Number Crunch'!B10,'YB_Monthly Placement File'!$E$14:$E$3000))</TD></TR><TR><TD>G10</TD><TD>=SUMPRODUCT(('YB_Monthly Placement File'!$AA$14:$AA$3000='Number Crunch'!$B10)*('YB_Monthly Placement File'!$AC$14:$AC$3000='Number Crunch'!$G$8)*('YB_Monthly Placement File'!$E$14:$E$3000))</TD></TR><TR><TD>H10</TD><TD>=SUMPRODUCT(('YB_Monthly Placement File'!$AA$14:$AA$3000='Number Crunch'!$B10)*('YB_Monthly Placement File'!$AC$14:$AC$3000='Number Crunch'!$H$8)*('YB_Monthly Placement File'!$E$14:$E$3000))</TD></TR><TR><TD>J10</TD><TD>=SUMPRODUCT(--(AllStates_Closed!$S$2:$S$273918='Number Crunch'!C9),--(AllStates_Closed!$H$2:$H$273918>='Number Crunch'!$J$1),--(AllStates_Closed!$H$2:$H$273918<='Number Crunch'!$J$2),--(AllStates_Closed!$G$2:$G$273918>'Number Crunch'!$J$8),--AllStates_Closed!$G$2:$G$273918<$L$8)--(AllStates_Closed!$Q$2:$Q$273918=$H$8)--(AllStates_Closed!$F$2:$F$273918)</TD></TR><TR><TD>L10</TD><TD>=SUMPRODUCT(--('YB_Monthly Payment Report'!$AO$16:$AO$4761='Number Crunch'!C9),--('YB_Monthly Payment Report'!$BE$16:$BE$4761=$H$8),--('YB_Monthly Payment Report'!$Z$16:$Z$4761))</TD></TR><TR><TD>C11</TD><TD>=SUM(C10)</TD></TR><TR><TD>G11</TD><TD>=SUM(G10)</TD></TR><TR><TD>H11</TD><TD>=SUM(H10)</TD></TR><TR><TD>J11</TD><TD>=SUM(J10)</TD></TR><TR><TD>L11</TD><TD>=SUM(L10)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Hi

Consider the last three conditions -

1, An open bracket is missing after the Double Unary of the first of the three conditions

2, There are no commas separating those conditions and

3, There is no final close bracket to terminate SumProduct.

hth
 
Upvote 0
J10:
Rich (BB code):
Rich (BB code): 
=SUMPRODUCT(
    --(AllStates_Closed!$S$2:$S$273918 = 'Number Crunch'!C9),
    --(AllStates_Closed!$H$2:$H$273918 >= 'Number Crunch'!$J$1),
    --(AllStates_Closed!$H$2:$H$273918 <= 'Number Crunch'!$J$2),
    --(AllStates_Closed!$G$2:$G$273918 > 'Number Crunch'!$J$8),
    --(AllStates_Closed!$G$2:$G$273918 < $L$8),
    --(AllStates_Closed!$Q$2:$Q$273918 = $H$8),
    AllStates_Closed!$F$2:$F$273918)


If this formula is on 'Number Crunch', omit this bit from the formula...

You seem to be on Excel 2007 or later. If so, invoke a SumIfs formula instead:

Rich (BB code):
Rich (BB code): 
=SUMIFS(
    AllStates_Closed!$F$2:$F$273918,
    AllStates_Closed!$S$2:$S$273918,'Number Crunch'!C9,
    AllStates_Closed!$H$2:$H$273918,">="&'Number Crunch'!$J$1,
    AllStates_Closed!$H$2:$H$273918,"<="&'Number Crunch'!$J$2,
    AllStates_Closed!$G$2:$G$273918,">"&'Number Crunch'!$J$8,
    AllStates_Closed!$G$2:$G$273918,"<"&$L$8,
    AllStates_Closed!$Q$2:$Q$273918,"="&$H$8)
 
Upvote 0
I used the SUMIFS as suggested (I just jumped over to 07 so I hadn't yet discovered SUMIFS) but the result is 0. I know there should be a value greater than 0. The formula looks like this:
=SUMIFS(AllStates_Closed!$F$2:$F$273918,AllStates_Closed!$S$2:$S$273918,'Number Crunch'!C9,AllStates_Closed!$H$2:$H$273918,">="&'Number Crunch'!$J$1,AllStates_Closed!$H$2:$H$273918,"<="&'Number Crunch'!$J$2,AllStates_Closed!$G$2:$G$273918,">"&'Number Crunch'!$J$8,AllStates_Closed!$G$2:$G$273918,"<"&$L$8,AllStates_Closed!$Q$2:$Q$273918,"="&$H$8)

Am I missing anything?

Regards
 
Upvote 0
Alex O said:
I used the SUMIFS as suggested (I just jumped over to 07 so I hadn't yet discovered SUMIFS) but the result is 0. I know there should be a value greater than 0. The formula looks like this:
=SUMIFS(
AllStates_Closed!$F$2:$F$273918,
AllStates_Closed!$S$2:$S$273918,'Number Crunch'!C9,
AllStates_Closed!$H$2:$H$273918,">="&'Number Crunch'!$J$1,
AllStates_Closed!$H$2:$H$273918,"<="&'Number Crunch'!$J$2,
AllStates_Closed!$G$2:$G$273918,">"&'Number Crunch'!$J$8,
AllStates_Closed!$G$2:$G$273918,"<"&$L$8,
AllStates_Closed!$Q$2:$Q$273918,"="&$H$8)

Am I missing anything?

Regards
Click to expand...

What is the name of the sheet that houses this formula?
 
Upvote 0
Alex O said:
Number Crunch
Click to expand...

Cleaned up...

Rich (BB code): 
=SUMIFS(
    AllStates_Closed!$F$2:$F$273918,
    AllStates_Closed!$S$2:$S$273918,C9,
    AllStates_Closed!$H$2:$H$273918,">="&$J$1,
    AllStates_Closed!$H$2:$H$273918,"<="&$J$2,
    AllStates_Closed!$G$2:$G$273918,">"&$J$8,
    AllStates_Closed!$G$2:$G$273918,"<"&$L$8,
    AllStates_Closed!$Q$2:$Q$273918,$H$8)

What does this do: Sum all F-values corresponding to S-cells
which are equal to C9 and corresponding to H-cells which are
between J1 and J2 and corresponding to the G cells above J8 and
below L8 while Q-cells are equal to H8.
 
Upvote 0
Thanks, Aladin – looks like its working. I’ll test more closely when I get back to my desk.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
Regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
619
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
574
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
660
Micron
M
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
J
  • Solved
Email body not showing using html.
Replies
3
Views
809
Logit
Logit

Forum statistics

Threads
1,224,541
Messages
6,179,418
Members
452,912
Latest member
alicemil

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