Using Sumproduct instead of Sumif / Countif

visitor83

New Member
Joined
Aug 5, 2010
Messages
14
Hi everyone,
Sorry but I can't get my head around this - I have created a workbook with a number of sumif & countif functions. However, I didn't realize that they don't work if the sheet that they are refering to are closed! I can't keep all the files open as there is way too much info.

I have a large range of numbers but I need to have it that if one cell in column b contains a value more than 60000, then it will take the value in column d and add it.

This is my SUMIF at the moment:
=SUMIF('[Revenue.xls]Sheet1'!$D:$D,"<60000",'[Revenue.xls]Sheet1'!$L:$L)

I know the SUMPRODUCT can not be an array, but that's ok, I can give it a reference.

I realise it's probably very simple, but I'm new to this, and it was working perfectly fine as a SUMIF, and I don't know how to convert it to the SUMPRODUCT although I've read a tonne of posts that say it works.

Thanks for anyone's help!

(also, is there something I can do for the countif?)
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

prabby25101981

Active Member
Joined
Jul 28, 2010
Messages
348
However, I didn't realize that they don't work if the sheet that they are refering to are closed!

I don't think that's the case. Can you try using Edit -> Links -> Update Links in your file? It should update the links automatically.

I know the SUMPRODUCT can not be an array, but that's ok, I can give it a reference.

SUMPRODUCT is actually an array formula only... I hope I understood you correctly...
 

visitor83

New Member
Joined
Aug 5, 2010
Messages
14
Ok, when I have the SUMIF function in place, and I close the workbooks that are being referenced, all the cells turn to #VALUE.

When I open the other workbooks back up, the cells recalculate.
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
Your correct, both sumif and countif only work with open workbooks.
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919

ADVERTISEMENT

SUMPRODUCT--(('[Revenue.xls]Sheet1'!$D:$D,"<60000"),('[Revenue.xls]Sheet1'!$L:$L))

try this
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
=SUMIF('[Revenue.xls]Sheet1'!$D:$D,"<60000",'[Revenue.xls]Sheet1'!$L:$L)

Equivelent Sumproduct formula

=SUMPRODUCT(--('[Revenue.xls]Sheet1'!$D1:$D1000<60000),'[Revenue.xls]Sheet1'!$L1:$L1000)

An important difference with sumproduct is that you CANNOT use entire column References like D:D.
That's why I added the Row #s.
Unless you are using XL 2007 or higher, but even then it is not recommended...seriously..

Hope that helps.
 

jc.021286

Well-known Member
Joined
Apr 12, 2010
Messages
725

ADVERTISEMENT

This is my SUMIF at the moment:
=SUMIF('[Revenue.xls]Sheet1'!$D:$D,"<60000",'[Revenue.xls]Sheet1'!$L:$L)

and
=sumprodct(--('[Revenue.xls]Sheet1'!$D:$D<60000),'[Revenue.xls]Sheet1'!$L:$L)

Does that work? does it update properly if you do what prabby said and go to update Links?

jc
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
Excel Workbook
JK
33.2534.5
43.25
53.25
63.50
73.75
84.00
94.25
104.50
114.75
125.00
135.25
145.50
155.75
166.00
176.25
186.50
196.75
207.00
217.25
227.50
237.75
248.00
258.25
268.50
278.75
289.00
299.25
309.50
319.75
3210.00
Sheet1


syntax like this
 

visitor83

New Member
Joined
Aug 5, 2010
Messages
14
Sorry but no, it's coming up with a different answer to the original sumif...and I can't tell why...

Can someone tell me what the "--" does in the formula?

And why do I keep getting a pop up to say that "Excel cannot complete this task with available resources. Choose less data or close other applications." - The file being referenced has nearly 10,000 rows but I only have my email, you guys and excel open at the moment! - I've never seen this message come up using excel before....?

Thanks
 

visitor83

New Member
Joined
Aug 5, 2010
Messages
14
Oh and yes - I have edited the formula that you guys gave from the range 1,000 to the 10,000...

So that's not why the figure gives a different total...! :LOL:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,838
Messages
5,833,921
Members
430,244
Latest member
Ireland1

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