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!
Answers
-
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")
-
@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.
-
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".
-
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!
-
Make sure you are using the appropriate steps to create the cross sheet reference to the dropdown column.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!