Referencing an existing field in a formula to perform a calculation based on the referenced field.

tesman284

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.

tesman284

Adam, thank you. I tried different combinations, but I did not try the exact one you sent. I appreciate your assistance.

Adam Murphy

Try COUNTIFS, something like: =COUNTIFS([Filter]:[Filter], [Vendors]$2, [Review Areas]:[Review Areas], CONTAINS("overall", @cell))

tesman284

Adam, thank you. I tried different combinations, but I did not try the exact one you sent. I appreciate your assistance.