Referencing an existing field in a formula to perform a calculation based on the referenced field.
I am attempting to build a template where I only need to change one field, based on that field value, in my worksheet that will update multiple formulas in the worksheet.
Is it possible to reference a field from within a formula that only using the referenced value in a particular column and row from the worksheet?
=IF(Filter@row = Vendors2, COUNTIF([Review Areas]:[Review Areas], CONTAINS("overall", @cell)), 0)
With this formula, I am attempting to use the information in the FILTER column to equal VENDORS listed in row two and then count the items in REVIEW AREAS if they contain Overall.
I have been able to get it to the total. You will see below the highlighted 21, but I should only return a total of 7, so it does not reference FILTER to equal VENDORS.
In short, if Vendor Row 2 is equal to SAP B1, find the rows within the column FILTER that are equal to SAP B1, then Count the Review Areas that only contain Overall based on this criteria.
Has anyone set up their Worksheet to perform a similar task? I'd appreciate any advice or examples.
Best Answer
-
Adam, thank you. I tried different combinations, but I did not try the exact one you sent. I appreciate your assistance.
Answers
-
Try COUNTIFS, something like: =COUNTIFS([Filter]:[Filter], [Vendors]$2, [Review Areas]:[Review Areas], CONTAINS("overall", @cell))
-
Adam, thank you. I tried different combinations, but I did not try the exact one you sent. I appreciate your assistance.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!