• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sumproduct formula

E

erwin

New Member
Hello,

I have set up an linearity file in excel 13 using the sumproduct formula this to find different kind of data for the correct cells. The formula itself works fine but the formula is causing errors on the performance read processors. I have to minimize the # of processors to 1 in order to get the file working to update the data or view. I am using tables for my data where the formula is getting its data. I have increased my RAM to 16GB and still I have to reset my # of processors to 1 in order to get the formulas working. I have not experienced this before. Does anybody know how to solve this issue. Thanks in advance.
 
Welcome to the forum!
I would guess that you may be over working the formulas. Are you searching just the range with data, or the entire column in a worksheet? How many SUMPRODUCT calculations are there?
 
Can you post a sample of the file or even several different Sumproduct formulas

I suspect as Luke said that you are referencing Whole Columns like C:C instead of ranges like C1:C10000
 
Hi ,

I am not able to understand why you will need to reset the number of processors to 1 ; see the following link , which clearly says that all native Excel functions run on one thread , which means they can use only one processor.

https://msdn.microsoft.com/en-us/library/office/bb687899.aspx

I may be wrong , but my understanding is that multiple threads can run on one processor , but one thread cannot run on multiple processors.

Or do you mean multi-threading needs to be disabled ?

You can have multiple threads whose number exceeds the number of processors , so that multiple threads can run on even a single-processor system.

If you disable multi-threading then Excel will not use all the processors available in the computer , but will do all its calculations using a single thread , which obviously will run on only one processor. However the performance improvements are not very significant unless the workbook has been designed badly , or designed very cleverly to take into account such hardware factors.

See this link for more information :

https://msdn.microsoft.com/en-us/library/aa730921(v=office.12).aspx

Narayan
 
Hello, thanks for the kind and quick answers.
To show you which formula I am using I will copy one in here.

The formula I am using is:
"=IF($B$51="EMEA",(SUMPRODUCT(--('1Q15 Data'!$B:$B="EMEA"),--('1Q15 Data'!$D:$D="OpenEdge"),--('1Q15 Data'!$F:$F="product"),--('1Q15 Data'!$I:$I="Pipeline"),--('1Q15 Data'!$O:$O="GFO AP Opportunity Record Type"),('1Q15 Data'!$H:$H))),(SUMPRODUCT(--('1Q15 Data'!$C:$C=$B$51),--('1Q15 Data'!$D:$D="OpenEdge"),--('1Q15 Data'!$F:$F="product"),--('1Q15 Data'!$I:$I="Pipeline"),--('1Q15 Data'!$O:$O="GFO AP Opportunity Record Type"),('1Q15 Data'!$H:$H))))".

I need to built in several variables in order to get a correct number.
I have set up the data in tab "1Q15 Data" in tables.

I am now referring to a column in my formula is this correct or should I have used the header of a column in the table like column B is "Product line" so it will select automatically that data in that particular column?

@Narayan: yes I mean multi threaded calculation. I have to reduce this to 1 processor only to get the calculation going. Which slows down the file big.

Thanks ahead for your response.
 
Hi Erwin ,

A few points :

1. SUMPRODUCT does not handle full column references well ; either you should use fixed references , or since you say your data is in tables , use structured references ; not only does that make the formula more readable and easier to create , it also speeds recalculation.

2. What happens if you eliminate the IF , and have just the following :

=SUMPRODUCT(--('1Q15 Data'!$B:$C=$B$51),--('1Q15 Data'!$D:$D="OpenEdge"),--('1Q15 Data'!$F:$F="product"),--('1Q15 Data'!$I:$I="Pipeline"),--('1Q15 Data'!$O:$O="GFO AP Opportunity Record Type"),('1Q15 Data'!$H:$H))

Narayan
 
I'd like to see all the negations removed as well as limit the ranges to say the first 10,000 rows
This will now be 1/100th of the calculations being asked before

=SUMPRODUCT(('1Q15 Data'!$B1:$B10000=$B$51)*('1Q15 Data'!$D1:$D10000="OpenEdge")*('1Q15 Data'!$F1:$F10000="product"f)*('1Q15 Data'!$I1:$I10000="Pipeline")*('1Q15 Data'!$O1:$O10000="GFO AP Opportunity Record Type"),('1Q15 Data'!$H1:$H10000))
 
Last edited:
Alright thanks guys I am going to work on both options to see how this works out.
I will look up how to set up the structural references.

Thanks again and I will let you know the outcome.
 
Hello Erwin,

If you are using Excel 2013, you could replace SUMPRODUCT with SUMIFS,

Try this version.

=SUMIFS('1Q15 Data'!$H:$H,IF($B$51="EMEA",'1Q15 Data'!$B:$B,'1Q15 Data'!$C:$C),IF($B$51="EMEA","EMEA",$B$51),'1Q15 Data'!$D:$D,"OpenEdge",
'1Q15 Data'!$F:$F,"product",'1Q15 Data'!$I:$I,"Pipeline",'1Q15 Data'!$O:$O,"GFO AP Opportunity Record Type")

B:B & C:C is the only changes based on value inB51. So you can use an IF formula to look proper columns. If you want to use SUMPRODUCT you can use same logic & limit the range.
 
You must log in or register to reply here.
Back
Top