Using Sumproduct instead of Sumif / Countif

[visitor83]

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

 

[prabby25101981]

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]

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]

Your correct, both sumif and countif only work with open workbooks.

 

[scottylad2]

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

try this

 

[Jonmo1]

=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]

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]

Excel Workbook
	J	K
3	3.25	34.5
4	3.25
5	3.25
6	3.50
7	3.75
8	4.00
9	4.25
10	4.50
11	4.75
12	5.00
13	5.25
14	5.50
15	5.75
16	6.00
17	6.25
18	6.50
19	6.75
20	7.00
21	7.25
22	7.50
23	7.75
24	8.00
25	8.25
26	8.50
27	8.75
28	9.00
29	9.25
30	9.50
31	9.75
32	10.00
Sheet1

syntax like this

 

[visitor83]

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]

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