How to adjust formula for stacked bar chart to use HAS or CONTAINS

Laura L

I have a stacked bar chart that displays how many initiatives are assigned to an individual by year. (See example below.)

We've recently changed the source sheet from only allowing one year per initiative to allowing for multiple years (for initiatives that span multiple years).

How do I adjust this formula to count if assigned to Valerie in 2025 (if an initiative also lists 2024 and 2026 in the Fiscal Year)? The correct resulting count of this formula should be 1.

=COUNTIFS({TEST President's Cabinet Initiatives Assigned To}, $[Primary Column]@row, {TEST President's Cabinet Initiatives Fiscal Year}, [Column4]$1)

Metrics/helper sheet for stacked bar chart:

Resulting bar chart:

Source sheet:

Any help would be greatly appreciated!

Laura

Paul Newcome

The syntax fo rthe HAS function would be:

=COUNTIFS({TEST President's Cabinet Initiatives Assigned To}, $[Primary Column]@row, {TEST President's Cabinet Initiatives Fiscal Year}, HAS(@cell, [Column4]$1))

Paul Newcome

The syntax fo rthe HAS function would be:

=COUNTIFS({TEST President's Cabinet Initiatives Assigned To}, $[Primary Column]@row, {TEST President's Cabinet Initiatives Fiscal Year}, HAS(@cell, [Column4]$1))

Laura L

Works perfectly. Thanks so much!

Paul Newcome

Happy to help. 👍️