sumproduct

Hello I am needing an alternative to a sumproduct funtion.


I have two sheets, one with the raw data (ID, Date and Code) and a separate sheet with ID, Code, and a field for total days. The raw data has multiple dates per ID and code, so I thought it would be a Sumproduct/countif combination, but found that sumproduct is not part of Smartsheets. Is there an alternative that doesn't require a report? Helper columns can be used.

Answers

  • Seth Morth
    Seth Morth ✭✭✭✭✭
    edited 08/13/22

    Hello @Desertbird!

    I am not clear on what you are going for, but you have some options.

    CONTAINS...***THE EXAMPLE BELOW WORKS IF THE STOCK CODE FIELD Contains 'SCON01' as PART of the TEXT, BUT NOT THE WHOLE TEXT IN THAT CEL*** 

    =IFERROR(SUMIFS({PO Detail_ReceivedValueInYear}, {PO Detail_StockCode}, CONTAINS("SCON01", @cell)), "")


    COLLECT...***THIS IS A BUSY EXAMPLE, IT DOES A LOT; Sums up the $ total of any orders based on Supplier Name (exact match), and meets the specified month (1=January) of the current year*** 

    =SUM(DISTINCT(COLLECT(OrderTotalCost:OrderTotalCost, SupplierName:SupplierName, ="Office Max", OrderEntryDate:OrderEntryDate, AND(MONTH(@cell) = 1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))))


    HAS...***THE EXAMPLE BELOW PULLS IN CEL CONTENTS, YOU CAN TOSS IN "AND" AND/OR "OR" TO HELP OUT; This one looks up a PO# and a PO Line# on Sheet-A and pulls in whether or not on Sheet-A how many have been received and displays it on Sheet-B (Where this formula lives)***

    =IFERROR(INDEX(COLLECT({CGC-Purchase_Received}, {CGC-Purchase_PO}, HAS(@cell, [PurchaseOrder]@row), {}, HAS(@cell, [PO Line]@row)), 1), "Not Found")


    There are a lot of people smarter than me on here who will chime in, but we all like to see screen captures and know column headers; with that someone will usually provide the actual answer for you. Is any of that available?

    Smartsheet also just updated this Formula Handbook, check it out!

    Have a great day!

    • Seth
  • Desertbird
    Desertbird ✭✭
    edited 08/14/22

    Thanks for your input, below is a sample of what the data will look like, each person has multiple dates and codes, I am trying simply to find how many days a person has with a specific comment, even if that comment appears more than once in a single day.



  • Thanks for your reply, I do have a sample of the data below. All I am trying to do is find out how many days a specific comment is used, and to count multiple instances in the same day as a single count.




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!