Sumproduct

H

Hoosier03

Board Regular
Joined
Apr 29, 2011
Messages
121
I am using Excel 2007. This is my equation

=IF(G$4="a",SUMPRODUCT(('AOP Query'!$G$22:$G$440=$C30)*('AOP Query'!$I$22:$L$440)),0)


The equation works in the original rows I had in the document. I inserted 4 new rows that reference 4 new account numbers ($C30 references account numbers) and they are all showing as 0 when I know there are dollar amounts for these account numbers.

Any idea what I could check to see where I went wrong?

Thanks,

Rodney
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Can you show a sample of your spreadsheet? It's hard to comment without seeing a sample.

Where did you insert 4 rows? Below row 440?
 
Upvote 0
Hoosier03 said:
I am using Excel 2007. This is my equation

=IF(G$4="a",SUMPRODUCT(('AOP Query'!$G$22:$G$440=$C30)*('AOP Query'!$I$22:$L$440)),0)


The equation works in the original rows I had in the document. I inserted 4 new rows that reference 4 new account numbers ($C30 references account numbers) and they are all showing as 0 when I know there are dollar amounts for these account numbers.

Any idea what I could check to see where I went wrong?

Thanks,

Rodney
Click to expand...
Does C30 house 4 account numbers as you seem to imply?
 
Upvote 0
<TABLE style="WIDTH: 265pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=353 border=0><COLGROUP><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6326" width=173><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 18pt" height=24><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 130pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" width=173 height=24>Salaried-Reg-Full</TD><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 79pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=105>600125</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 56pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=75>$81,783 </TD></TR><TR style="HEIGHT: 18pt" height=24><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>Sal-Reg-Full-OT</TD><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">600140</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 18pt" height=24><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>Salaried - Cas Ong</TD><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">600180</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 18pt" height=24><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>Sal-Cas Ong OT</TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">600185</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>FT Hourly </TD><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">600035</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$496,410 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>FT Hourly OT</TD><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">600045</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$22,303 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Casual Ongoing </TD><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">600025</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$3,662 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Casual Ongoing OT</TD><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">600030</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Hourly Casual Limited </TD><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">600060</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$26,832 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Casual Limited OT</TD><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">600065</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Hourly-Daily Hires Hours</TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">600090</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Hourly-Daily Hires Hours OT</TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">600095</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Hourly-Casual Ongoing</TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">601055</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Hourly-Regular Full</TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">601060</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Hourly Full Time-YE</TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">601065</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Hourly Casual Limited </TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">601075</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Settlement Labor</TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">600810</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Non Disney Benefits</TD><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">601100</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Transfer Labor</TD><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">930000</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Transfer Labor-Plan</TD><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">930005</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>($92,998)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Replacement</TD><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">604010</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl541 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Replacement OT</TD><TD class=xl543 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">604015</TD><TD class=xl542 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>$0 </TD></TR></TBODY></TABLE>


The inserted rows are in red. This is on a different worksheet than the SUMPRODUCT is referencing.
 
Upvote 0
And the, you also have a cell with a SUMPRODUCT like this one:

=IF(G$4="a",SUMPRODUCT(('AOP Query'!$G$22:$G$440=$C30)*('AOP Query'!$I$22:$L$440)),0)

but with $C30 being a cell with 601055? If not it's logical that the sum is 0 I would say.
 
Upvote 0
What I am confused on is the equation is working for the rows that I did not insert, but not for the rows that I inserted.
 
Upvote 0
This is the formula.


=IF(G$4="a",SUMPRODUCT(('AOP Query'!$G$22:$G$440=$C30)*('AOP Query'!$I$22:$L$440)),0)

I just dragged it down.
 
Upvote 0
What are the contents of the cells C30:C... ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

datatronics505
  • Solved
Absolute reference to a cell of another table
Replies
5
Views
204
datatronics505
datatronics505
J
  • Question Question
Sumif vs Sumproduct
2
Replies
11
Views
621
etaf
etaf
N
  • Solved
Sumproduct with Table Rows doesn't work
Replies
8
Views
295
Nicha
N
A
  • Solved
If AND Formula Help
Replies
3
Views
346
dreid1011
dreid1011
D
  • Solved
Remove Absolute References
Replies
3
Views
179
HighAndWilder
H

Forum statistics

Threads
1,224,505
Messages
6,179,152
Members
452,891
Latest member
JUSTOUTOFMYREACH

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