Using Countifs in a cross reference formula

Hello - This is the formula I am using: =COUNTIFS({Company RAID Log Range 1}, "XXXXXX", {Company RAID Log Range 2}, "CVI")

Company RAID Log Range 1 = LEAD Column

Company RAID Log Range 2 = Ownership Column

where XXXXXX is a person's name. This is what I hope is returned: count of times this persons name is list in the LEAD column AND the OWNERSHIP TEAM = CVI.

This information is in the same Worksheet. I know the person is named the lead 45 times. When I use this formula the counts returned are wrong. For instance - for Ownership Team = FINANCE =43 but my formula is saying 27. When I ask for the count of ownership team = CVI my formula returns 5 but person XXXXXX is only the LEAD for 2 of them.

Person XXXXXX is not responsible for any activities where the ownership Tema = supply chain but my formula brings back an answer of 3.

What I am attempting to count is this: In the RAID LOG for person XXXXXX how many activities are they responsible for by Ownership Team? So I want to use my 1st range to limit to the person and the second range to return the count

Do I need an AND? or is my formula backward? Different Formula?

Thanks in advance for your help!!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!