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]

It sounds like you've just got way too much calculation going on in that book.

Especially considering your posted sumif formula used Entire Column Refs like D:D..
That's just begging for performance problems...

Can you create a new book, and copy just the relevant data from the old one into the new one, then try it.

 

[visitor83]

Actually this datasheet has been copied and pasted...and most of the information is needed for a variety of sheets now...

Although... =) ...through the copy and paste, there were hyperlinks which are not required...I've taken those and all the corporate logo graphics etc out...

The #REF! error has now gone...and the results from the sumif and the sumproduct, matches! =) yey!...

I did try to take out the <"" section though, and it then does pull through the incorrect value from before...so that is going to need to stay! =)

Do you have any tricks for searching for text using the sumproduct?....This is the equvilant on the sumif:
=SUMIF('[Revenue.xls]Sheet1'!$D1:$D10000,"pwifi*",'[Revenue.xls]Sheet1'!$K1:$K10000)

But because of the text...it won't work?

 

[visitor83]

I gotta run, I have a meeting to get to, but will check back later =)

You have been so great and I hope you can continue to help!

Vic

 

[Jonmo1]

Try

=SUMPRODUCT(--(LEFT('[Revenue.xls]Sheet1'!$D$1:$D$10000,5)="pwifi"),'[Revenue.xls]Sheet1'!$K$1:$K$10000)