Pivot table with countifs using multiple criteria

Hello All,

I am trying to build a pivot table using the countifs formula that looks at a spreadsheet containing two various criteria - one is by line of business and the other is if a selection = Yes from a drop down menu - they are in two separate columns but within the same spreadsheet and the pivot table is its own spreadsheet. I had to add in a test column and insert a yes or the countifs formula I have counts every single line. The counts seem to switch to the correct counts if I have the test line in. Please see below for the current formula I have going:

=COUNTIFS({FutureMakers: Managers+ Range LOB}, [Primary Column]@row, {FutureMakers: Managers+ Range Selection Status}, {FutureMakers: Managers+ Range Yes})

Without the test column marked as yes the counts look something like this:

TS 123

CS 321

AS 456

ICS 657

CT 789

But with the test column marked as yes the counts look like this:

TS 0

CS 0

AS 0

ICS 0

CT 0

The hope is that as selections are made and marked as yes the counts will update on an ongoing basis. This happens as long as the test column is in place but I'd love to not have to use that moving forward.

What am I missing? Please help! Thank you!

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Your final {Range} should just be the criteria of the range before which would be "Yes".


    =COUNTIFS({FutureMakers: Managers+ Range LOB}, [Primary Column]@row, {Dropdown}, @cell = "Yes")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome thanks for your response. Is what you put in the full formula I should insert or just part of it? I put it in to test and got unparseable and invalid operation.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You will need to update your existing formula. The bold portion in mine is the update needed. You need a range/criteria set of looking down the dropdown column (cross sheet reference) with the criteria being @cell = "Yes".

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Original Formula:

    =COUNTIFS({FutureMakers: Managers+ Range LOB}, [Primary Column]@row, {FutureMakers: Managers+ Range Selection Status}, {FutureMakers: Managers+ Range Yes})

    Proposed New Formula:

    =COUNTIFS({FutureMakers: Managers+ Range LOB}, [Primary Column]@row, {Dropdown}, @cell = "Yes")

    The proposed new formula says invalid reference. The dropdown is in a different column on the spreadsheet. What am I missing? Thanks so much in advance for the help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Make sure you are using the appropriate steps to create the cross sheet reference to the dropdown column.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!