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")
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!
-
@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".
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!
-
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.
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!