I have two columns; one consisting of a list, using Join/Collect, of items, and one consisting of a formula to look up and sum the prices of the Join/Collected items.
When the list (column 1) of items consists of only one item, the formula (column 2) works fine. When there are multiple items in Column 1, the formula does not sum to the total cost of the items, and returns 0 (possibly indicating that it can't find a match and thus no price to sum).
Column 1 is a list of items, in this case, room spaces:
The Column 1 spaces are gathered with this formula (which works fine visually):
=IF(ISBLANK(JOIN(COLLECT([Alternate Cypress Creek Campus Event Space]@row :[Alternate South Austin Campus Event Space]@row , [Alternate Cypress Creek Campus Event Space]@row :[Alternate South Austin Campus Event Space]@row , ISTEXT(@cell )))), JOIN(COLLECT([Highland Campus Event Space Requested]@row :[Service Center Campus Event Space Requested]@row , [Highland Campus Event Space Requested]@row :[Service Center Campus Event Space Requested]@row , ISTEXT(@cell )), CHAR(10)), JOIN(COLLECT([Alternate Cypress Creek Campus Event Space]@row :[Alternate South Austin Campus Event Space]@row , [Alternate Cypress Creek Campus Event Space]@row :[Alternate South Austin Campus Event Space]@row , ISTEXT(@cell )), CHAR(10)))
The formula for column 2 is as follows:
=SUM(COLLECT({Half Day Pricing}, {Service Offering}, HAS([List of collected Items]@row , @cell )))
where "half day pricing" is a column in another sheet with prices, and "service offerings" is a column in the same other sheet with a list of items offered.
What is my SUM formula doing wrong?
