How can I count items only when 3 specific criteria are met?
Hello All,
Novice user here -
I am building a data sheet for metrics and am hoping someone could please help me figure out the best way to create a COUNTIF/IFS formula that will only count a status 'type' (Complete, Scheduled, etc.) if the Core OpCo is 'FLA' and the year is 2022 and the LFCM OT box is 'checked/True'?
I'm not sure where to begin, or if using COUNTIF/IFS is the correct way to go about it?
Answers
-
COUNTIFS would be your best place to start. It would look Something like this:
=COUNTIFS([DC UPS Status]:[DC UPS Status], "Complete", [Core OpCo]:[Core OpCo], "FLA", [DC UPS Year]:[DC UPS Year], "2022", [LFCM OT DC UPS Scope?]:[LFCM OT DC UPS Scope?], 1)
See if that returns what you are looking for.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
Hello Kleerfyre,
I REALLY appreciate your help!
I tried using the formula, but I didn't get the results I needed unfortunately. It returns #unparseable. I need to have it count these status types from a column in another sheet and pull it into my metrics sheet - that is one part that I neglected to mention. Is it possible to use a COUNTIFS in conjunction with a VLOOKUP?
Or maybe there is a better way to go about it?
-
Hi @PKane
You can turn a COUNTIFS formula from referencing columns in-sheet to use cross-sheet references! For example:
=COUNTIFS({DC UPS Status column}, "Complete", {Core OpCo column}, "FLA", {DC UPS Year column}, "2022", {LFCM OT DC UPS Scope column}, 1)
Here are some links that may help you:
COUNTIFS Function / Create cross sheet references to work with data in another sheet
However before changing up the references we should sort out why you're getting an error with Jonathan's formula. Can you post a screen capture of what you tried in the cell? We'll need to double check that all the column names are spelled properly and that the formula syntax is correct.
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!