Pivot table with countifs using multiple criteria

Emily Long

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!

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")

Emily Long

@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

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".

Emily Long

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

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