Get instant live Excel expert help with SUMPRODUCT Function

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.

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.

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.

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