sumproduct function

  1. J

    sumproduct

    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...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top