# Using Sumproduct instead of Sumif / Countif

#### visitor83

##### New Member
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
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
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.

##### Well-known Member
Your correct, both sumif and countif only work with open workbooks.

##### Well-known Member

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

try this

#### Jonmo1

##### MrExcel MVP
=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

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

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
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
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...!

Replies
11
Views
502
Replies
3
Views
156
Replies
3
Views
94
Replies
13
Views
1K
Replies
3
Views
106

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.

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.

### Which adblocker are you using?

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

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