sumproduct function

  1. J


    Hi everyone, Need help from you guys. TO avoid having #value, I have to use sumproduct formula, can I include <"01/01/2015" within the sumproduct formula as I need to sumif the column B under the condition of before 2015 and after 2015 without creating a new column. Many thanks, Jess
  2. C

    Using SUMPRODUCT with different array lengths

    I'm trying to copy a SUMPRODUCT formula to other cells, but I have different array lengths. Is this possible or will have to continue to copy and paste the formula to the new cell and manually adjust the arrays? Is there a way Excel can find the breaks/array length difference? Thanks, RJ
  3. C

    use sum product on cells that contain formulas

    I have the current formula to use as a count based on 2 criteria. =SUMPRODUCT((TEXT(Order!A2:A65535,"m/dd/yy")=TEXT('Order Confirmation'!$L$13,"m/dd/yy"))*(Order!H2:H65535>=1)*(Order!A2:A65535<>"")*(Order!H2:H65535<>"")) Column A on my Order sheet is an entered date and L13 is a specific date...
  4. F

    SUM per City

    hi, I have simply formula SUM: =SUMPRODUCT('Sheet3'!$BF$2:$BF$100*'Sheet3'!$AX$2:$AX$100*(Specific_Name=A10)) In coulmn BB I have name of the CITY - Tokyo, Paris, Londyn etc... Now I want to SUM exactly this same but depending on the selected city (example: 'Sheet1'!A1= 'Paris') Could you...
  5. M

    =SUMPRODUCT ..... Help Please

    Hi there SUMPRODUCT is giving me a headache as I can't figure out where I am going wrong. I have a spreadsheet with data in Columns A to W, Column E I have Named as TSStatus and Column U has been Named TSCom. TSStatus has a range E2:E20000 and holds information "Not Started", "Pending"...
  6. J

    Anyway to use OFFSET & MATCH together?

    I have two spreadsheets in the same workbook. Both spreadsheets a have a similar layout for the monthly columns portion. 12 columns with the "month" name for column headers and below each monthly header a numerical #. For the first 12 months 1 -12 entered under each month respectively...
  7. D

    Looking for a simplified SUMPRODUCT solution

    Hi, I have one column with code letters such as T, S, P, O, the other column values. I want to sum the values when a corresponding matching letter is present. =SUMPRODUCT(--(CAT="T"),DV12:DV74) Works, but =SUMPRODUCT(--(CAT="T"),--(CAT="O"),DV12:DV74) does not. I even tried...
  8. S

    Complicated(?) Cross Sum function

    Hi All, Does anyone know a formula for the following problem (I have up to 120 iterations to do and the thought of typing them all out makes me want to quit) I have a response curve and a number of volumes and want to sum up the value of the responses e.g.: <TABLE style="WIDTH: 333pt...
  9. M

    Is this Possible?

    Is it possible to embed an if-then-else statement into a sumproduct function? For example: will this work: =Sumproduct((table1[Location]=$A$1)*(table1[ProductType]=$A$2)*(if(table1[Period]="Qtr1",1,0))*(table1[extension])) I have tried it and it just basically "ignores" the period...
  10. K

    Getting my head around SUMPRODUCT

    Below is an example of a sheet in which I collect data on the quality of the work each employee delivers. From column H onwards I register if an employee did the CPI (Critical Process Indicator) correctly or not. One mistake and the formula in column D goes to 'no', but if all the indicators in...
  11. M

    Troubleshooting SumProduct #VALUE error

    I'm relatively new to using SUMPRODUCT and haven't ever had to troubleshoot the formula when it isn't working. I've got the following construct: Month: Numbers 1-12 Action: text list of 14 different actions taken (e.g., new, modify, write-off Type: text list of 5 difference types (e.g., grant...
  12. B

    Find() nested in Sumproduct (Array Formulas)

    I'm trying to come up with a formula to conditionally sum the parts of cells in an array. Column A A-8 x x x S-2 s-1.75 a-9.5 So I'm trying to find the sum of values that begin only with s, either caps or not. I think this'll be a sumproduct, but I am running into trouble when I try to insert...
  13. D

    Nesting an IF statement in a SUMPRODUCT

    Windows XP / Windows 2007 I use SUMPRODUCT to pull data to a summary sheet based on the criteria of a date range and company name. The summary sheet can be an individual company or a consolidation of all companies, aka "All". I currently use the following formula where B13 is the company...
  14. M

    Sumproduct question

    Hello, I have 3 seperate spredsheets with our member's info's. Each spreadsheet has the same # of columns and labels but different member info's. Here is an example of one of the spreadsheets. <TABLE style="WIDTH: 283pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=377...

Some videos you may like

This Week's Hot Topics