sumproduct

  1. R

    Fancy sumproduct / index lookup

    Hi, Apologies for not uploading a workbook - company restrictions prevent me from this However, I need to consolidate the employee data in table 2 at the very bottom and present it as per table 1 below this message. the main issue I'm encountering is that the pay category runs across the row...
  2. T

    Count instances in a month and ignore blanks

    Hello, I am using the formula below to count the number of incidents in the month of January =SUMPRODUCT(1*(MONTH('Data Sheet'!$P$2:$P$1009)=1)) There are only about 30, but it is returning 900. It seems to be counting blanks as January. Is there a way blank cells can be ignored? Thank you,
  3. sdkorin

    Count SUMPRODUCT Match and a second criteria

    Greeting! I'm trying to figure out how to count a specific group of people in a workbook. My workbook is for staffing and name populate out of another workbook with specific character formatting (special characters are attached to the name specifying gender and certifications) Generally peoples...
  4. S

    Issues getting Sumproduct working with Indirect

    Hi A column contains a header which is a named Cell. I want to return number of Items in the entire worksheet column (the column containing the named Cell). I'm using the count to create dynamic ranges, it's all automated and needs to be reliable... CountA is not returning correct answers...
  5. M

    Searching an array of cells for a value and returning the column headers where a value exists

    Hi, I'm trying to find a way which i can return the column headers where a value exists in the cells F:M for the same row. As you can see from the screenshot for the first row, i would like to search for any value in row 3 and bring back the column heads for the rows. I have shown in Column N...
  6. S

    Ticket Prices: Date Ranges

    I am trying to calculate how much someone needs to pay me, where the ticket prices are set for a certain period. I have already have help to get this far, but I'm struggling to work out why the formula would work in Office365 and not Office 2013 In Cell F2...
  7. Johnny Thunder

    Formula Help - SumProduct Formula to return value only once in the event of duplicate row

    Hello all, I have a spreadsheet that contains a list of PO Numbers, the list may at times have the same PO repeated on different rows. I need a modification to my formula to be able to only show the result once if there is a duplicate row. Not sure of a way to do this so hopefully someone has...
  8. F

    Help Needed Combining Index/Match with Sum across Different Sheets!

    Hi All, this is my first time posting here. I have done a lot of google searching but i haven't found exactly the problem to my issue. I have multiple sheets which i want to add across. the rows and columns are not in the same order, therefore, a simple sum across sheets wont work. I want to...
  9. R

    Fancy lookup required! :)

    Hi, I need a formula that can identify when the Employee in column A leaves the business. This can be seen from the -1 in the respective months. For example Employee 1 leaves the business in P03. This is populated below the data and this is where I'd like the formula to work from if possible...
  10. F

    Exclude year with sumproduct and subtotal

    Hey guys, I've recently started to use the sumproduct and subtotal functions to make my data responde to filters and after some struggle i made it work, but right now i can't use an expression that excludes by year. So i need an expression that gives me the number of dates in a column that...
  11. J

    Sumifs / sumproduct that contains multiple columns

    Hi excel experts! I'm trying to use sumifs that contains multiple columns...So I want to get the value highlighted in red if Color1, Color2, and Color3 contains "red", based on its assigned values. I was trying SUMIFS and SUMPRODUCT, but couldn't really figure it out. <tbody> code (columnA)...
  12. JTL9161

    Sumproduct with an IF statement

    I have this formula below. I need to add and if statement to it that if the total is less than 4 (<4) then just put an 0 but if 4 or greater (>4) then show the number. =SUMPRODUCT(COUNTIF(R2:U2,B$192:Q$192)) Thank You, James
  13. L

    COUNTIF / SUMPRODUCT with WEEKNUM

    Hello Guys. A quick and simple question (too difficult for me though). I have a column with dates (Column A). I need to count the number of records that are from the certain year (YEAR formula) and certain week (WEENNUM) without using any additional calculation columns (file is big, a lot of...
  14. R

    Replicate SUMPRODUCT in VBA to create UDF which is much shorter to simplify appearance of formulae

    Hello, I would like to know how to create a UDF in VBA that replicates a SUMPRODUCT formula. Is this possible and how can I do it? I am new to VBA so not familiar enough with the code to create this myself. For example - UDF - HO() = SUMPRODUCT((Item Index range=X)*(Date Index...
  15. A

    Excel - Sumif/Sumproduct Not Working; Criteria contains partial text

    I need your help in getting the right formula for this. In column B, I have a list of items called Support Type. There're just 2 types, 535 and 532. These are taken over from a larger table with a formula. Column D lists the Employee Names. Also taken over from another table with a formula...
  16. K

    Sumproduct Binary - multiple criteria in the same range

    I need to sum deposits for each account number for deposits that have valid codes. I've tried SUMIFS and SUMPRODUCT, and run into the same problem of setting criteria to a range of values (E2:E6). Here is where i'm at from a SUMPRODUCT path: =SUMPRODUCT(--($A:$A=$G2),--($C:$C=$E$2:$E$6),$B:$B)...
  17. G

    SUMIFS between two different workbooks (SUMPRODUCT solution needed)

    Hi, I have recently discovered that SUMIFS only works between two different workbooks if both workbooks are open at the same time. I've also read to get round this, SUMPRODUCT can be used. That said I can't get it working. The SUMIFS version of the formula I need converting to SUMPRODUCT is...
  18. Johnny Thunder

    Formula Help - SumProduct with multiple Conditions that include a wildcard

    Hello All, I am hoping this is an easy one, I have a sumproduct formula that looks at multiple conditions and it works great but the business just threw in a new variable and I was hoping it will be a quick modification to the formula to get it to work. Here is the formula...
  19. M

    Excel Sumproduct formula with Wild Card

    Hi Team, Need your help , in sumproduct formula, I want Count of Partial Match. and also how to use not Equal in Partial Match using wild Card. Below excel formula gives correct result. for Exact Match. Need some change in Third Criteria. highlighted in bold...
  20. E

    Using SUMPRODUCT with multiple dropdown menus

    Hi, all! I've got a calendar-style sheet (sheet 3) that I'm pulling data from, and I've got the categories in 2 different dropdown menus on a second sheet (sheet 4). I'm trying to get a cell to add up all of the occurrences of certain text within the calendar. I've figured out how to...

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top