sumproduct

Desertbird

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.

Seth Morth

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

https://community.smartsheet.com/discussion/comment/339579#Comment_339579

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.

Desertbird

https://community.smartsheet.com/discussion/comment/339579#Comment_339579

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.