What function do i use to retrieve the count from multi category?
EG: In Column #1 i have three categories:
- Internal
- External
- Carrier
In Column #2 i have
- PC
- Phone
- Tablet
I want to know the count for PC for internal seperate as well as the counts for external and carrier. I would like to repeat this for the phone and tablet. Any suggestions?
Answers
-
Try a COUNTIFS.
=COUNTIFS([Column1]:[Column1], @cell = "Internal", [Column2]:[Column2], @cell = "PC")
-
I tried that but it is not working. I should say that i am trying to make a report from a different sheet
-
Is it giving an error or an incorrect number?
-
it is giving me an error. this is the formula i put in
-
You need to use a COUNTIFS (with the "S" on the end) to have multiple range/criteria sets, and it looks like you have your ranges set up to look at columns on the same sheet. If you are wanting to reference another sheet, then you would need to create your ranges with cross sheet references.
Start typing
=COUNTIFS(
Click on the blue text in the formula helper box that says "Reference Another Sheet".
Select the appropriate sheet you want to reference from the list on the left.
Click the column header that contains the data you want to evaluate for that particular range.
Optional: Rename the range.
Click on the "Insert Reference" button in the bottom right corner.
It should then take you back to the sheet you are working in and you will now have
=COUTNIFS({Range Name}
Enter a comma then enter your criteria, comma, set up second range, comma, second criteria, closed parenthesis.
-
This works
This work for me.
Now i do now an issue that arise. When i use the formula, it only counts the cell that has the work "hhp" alone, i would like it to count when "hhp" is also selected with another category item. is there any modification i can do in the formula?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!