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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!