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

 

[Jonmo1]

Are you sure it's not the row #s?

Change your sumif to use the same row numbers instead of the entire columns...


These 2 formulas will return EXACTLY the same result.

=SUMIF('[Revenue.xls]Sheet1'!$D1:$D10000,"<60000",'[Revenue.xls]Sheet1'!$L1:$L10000)
 
=SUMPRODUCT(--('[Revenue.xls]Sheet1'!$D1:$D10000<60000),'[Revenue.xls]Sheet1'!$L1:$L10000)

 

[scottylad2]

Do all the cells referred to have numbers in, as in no dates or texs? Sumif and countif will ignore other elements but sumproduct will not, and it will try to include it in the evaluation.

 

[visitor83]

Ok so I've tried this in one of my other cells, and it is working great when I am referencing another cell now...

But when I am asking it to search for the the 'less than 60000' it still isn't working...

And I'm also wanting to adapt it for another cell to search for all cells in the range that have...."pwifi*"...but that doesn't seem to be working on the sumproduct...either...help! =)

 

[visitor83]

No they are not only numbers....some of the cells in the range have text in them too...can I work around this?

 

[visitor83]

The sumif gives a value of 2313 which I've checked now by sorting and summing, but the sumproduct gives a value of 72839...

And I've changed the cell referencing in the sumif formula to match the cell references in the sumproduct to be sure too... =(

 

[Jonmo1]

Something is not right...

Do you want to send me your book?
PM me for my email..

 

[Jonmo1]

Wait......

Are there any Blanks in Column D?
Sumproduct will consider blanks as less than 60000, sumif/countif will not...

try

=SUMPRODUCT(--('[Revenue.xls]Sheet1'!$D1:$D10000<60000),--('[Revenue.xls]Sheet1'!$D1:$D10000 < > ""),'[Revenue.xls]Sheet1'!$L1:$L10000)

<!-- / message --><!-- sig -->

 

[visitor83]

Nope...no blanks in the column...except at the end...I mean there aren't exactly 10,000 rows...there are 9942 on this occassion, it's just that on other books to be referenced there will be up to 10,000...maybe more I guess, but I can edit that later if needed...

 

[Jonmo1]

Try the revised formula anyway...because it will be considering blanks in column D, if there are stray numbers in column L where there are blanks in column D, they will be added.
Post back after doing that..

 

[visitor83]

I have switched it as you suggested but I'm getting the excel pop up box error saying "Excel cannot complete this task with available resources. Choose less data or close other applications."

And then the cell gives a #REF! error?