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?)
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

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:
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,713
Messages
5,512,996
Members
408,930
Latest member
Michealker

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top