Get instant live Excel expert help with SUMPRODUCT Function

“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Our professional experts are available now. Your privacy is guaranteed.

Here are some problems that our users have asked and received explanations on

I need to create a formula that counts the number of jobs matching specific criteria (e.g. overdue, due this week, due this month), over three sheets in the same file; AND displays the count in a cell- which I can then double click to bring up a table of those jobs (similar to pivot tables). I need to be able to save this formula in a template as the source data will change every week and I don't want to have to go through the bother of tweaking pivot tables every time. I've had no luck with countifs and sumproduct, as i cant seem to combine the three source data, and getting the cell "double click"option to bring up a table either.
Solved by V. S. in 19 mins
HI, I need to verify that a formula I was given is going to work correctly. I have a 200K row spreadsheet and the results are not producing what I expect. In a small file that was used to test the formula, it appears to work. The formula is =SUMPRODUCT(($A:$A=A2)*(B2>$B:$B))+1 I need to take value in A, compare it to value in B, and then in the next column, rank the items in B numerically. However, the values in column A and B frequently change. So there may be results 1, 2, 3 or 1,4,5,3,2 (I will need to sort to get the results in numeric order). I do know sumproduct is an extremely resource intensive formula.. Thank you.
Solved by K. D. in 18 mins
I have been trying to count a column of data based on specific criteria. The data in column A is actually in an external workbook named book6.xlsx (for simplicity I am showing it all in the same workbook) and I am using the SUMPRODUCT function to try to count how many times any time 4* occurs in cell C2, however I need to keep any values that begin with 4B from being counted as part of 4* and count 4B* seperately in D2. I have additional information to provide, including a formula, but am exceeding the character limit.
Solved by B. H. in 18 mins
I am trying to use a sumproduct formula that is reference a data range in which the data values are formulas. Is this possible, it is giving me a #value! error for the formula. When I select a different data range that is not formula based there is no #value! error to my formula
Solved by S. H. in 24 mins