Sumproduct formula required

[kanedmamma]

I am using the following formula which works, however I would prefer to use sumproduct as the data is in another worksheet and I want the results to refresh without having the other worksheet open. I cannot get the formula to work using sumproduct - any suggestions? Thanks in advance

Basically the formula returns a value from column L if column I is between 2 dates and column J contains a specified text

=SUMIFS('S:\Sales\Masters\EBMS weekly statistics DO NOT USE\[EBMS Chase Log.xls]Chases 11-12'!$L$2:$L$2000,'S:\Sales\Masters\EBMS weekly statistics DO NOT USE\[EBMS Chase Log.xls]Chases 11-12'!$I$2:$I$2000,">=01/04/11", 'S:\Sales\Masters\EBMS weekly statistics DO NOT USE\[EBMS Chase Log.xls]Chases 11-12'!$I$2:$I$2000,"<=30/04/11",'S:\Sales\Masters\EBMS weekly statistics DO NOT USE\[EBMS Chase Log.xls]Chases 11-12'!$J$2:$J$2000,"banqu")

 

[RichardMGreen]

Try this:-
=sumproduct(('S:\Sales\Masters\EBMS weekly statistics DO NOT USE\[EBMS Chase Log.xls]Chases 11-12'!$I$2:$I$2000>=01/04/11)*('S:\Sales\Masters\EBMS weekly statistics DO NOT USE\[EBMS Chase Log.xls]Chases 11-12'!$I$2:$I$2000<=30/04/11)*('S:\Sales\Masters\EBMS weekly statistics DO NOT USE\[EBMS Chase Log.xls]Chases 11-12'!$J$2:$J$2000="banqu")*('S:\Sales\Masters\EBMS weekly statistics DO NOT USE\[EBMS Chase Log.xls]Chases 11-12'!$L$2:$L$2000))

 

[kanedmamma]

Just tried your formula Richard but I am getting a vaule of nil (not the correct value)

PS
I entered as both a 'nested' formula and as normal

 

[RichardMGreen]

Can you post some sample data? The formula should work providing all the conditions are true.

 

[kanedmamma]

Can I attach a file or email you file?

 

[RichardMGreen]

No need, just post the data on here using one of the options in the sticky at the top of this board.

 

[kanedmamma]

Sheet 1 data

<TABLE style="WIDTH: 450pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=597><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=74>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 46pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=61>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=58>C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=62>D</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=68>E</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=62>F</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 40pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=53>G</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=72>H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=87>I</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>MCKENZIE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>01/02/11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>13036</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>LOCAL</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>29/10/11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>29/10/11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>BANQU</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>5000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>£500</TD></TR></TBODY></TABLE>

Sheet 2 data (where formula is)

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9; WIDTH: 48pt; HEIGHT: 18.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=25 width=64>Oct</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Nov</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Dec</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Jan</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Feb</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Mar</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD></TR></TBODY></TABLE>

Value under Oct should be £500

I am using excel version 2010 not sure if there are any changes to the way formulas work - I know there are new ones such as the SUMIFS that I am currently using

Is this all the information you need? One more thing to note is that both files are on mapped network drives

 

[Aladin Akyurek]

I am using the following formula which works, however I would prefer to use sumproduct as the data is in another worksheet and I want the results to refresh without having the other worksheet open. I cannot get the formula to work using sumproduct - any suggestions? Thanks in advance

Basically the formula returns a value from column L if column I is between 2 dates and column J contains a specified text

=SUMIFS('S:\Sales\Masters\EBMS weekly statistics DO NOT USE\[EBMS Chase Log.xls]Chases 11-12'!$L$2:$L$2000,'S:\Sales\Masters\EBMS weekly statistics DO NOT USE\[EBMS Chase Log.xls]Chases 11-12'!$I$2:$I$2000,">=01/04/11", 'S:\Sales\Masters\EBMS weekly statistics DO NOT USE\[EBMS Chase Log.xls]Chases 11-12'!$I$2:$I$2000,"<=30/04/11",'S:\Sales\Masters\EBMS weekly statistics DO NOT USE\[EBMS Chase Log.xls]Chases 11-12'!$J$2:$J$2000,"banqu")

Try...

=SUMPRODUCT(
X!$L$2:$L$2000,
--(X!$I$2:$I$2000>="01/04/11"+0),
--(X!$I$2:$I$2000<="30/04/11"+0),
--(X!$J$2:$J$2000="banqu"))

Replace the X bit with:

'S:\Sales\Masters\EBMS weekly statistics DO NOT USE\[EBMS Chase Log.xls]Chases 11-12'

 

[kanedmamma]

Perfect - many thanks for your patience and help. What do the - - do in the formula?

 

[Aladin Akyurek]

Perfect - many thanks for your patience and help.

You are welcome. Thanks for providing feedback.

What do the - - do in the formula?

Here some links on the matter in no particular order:

http://www.mrexcel.com/forum/showthread.php?t=202204

http://www.mrexcel.com/forum/showthread.php?t=128907

http://www.mrexcel.com/forum/showthread.php?t=70547