conditional count
Hello good people,
I'm simply trying to come up with a formula that counts the occurrences of a specified value from a drop down list column (even if there are multiple values selected in cell within that column). There are 2 other conditions as well with designated columns: specified date and person.
This formula is drawing from another sheet and after about 30 minutes of research this was the closest i could come up with:
=SUM(IF((ISNUMBER(SEARCH("Completed", {BMDA TAP QA Sheet Range 5}))) * ({BMDA TAP QA Sheet Range 1}="2023-02") * ({BMDA TAP QA Sheet Range 2}="John Doe"),1,0))
I've tried multiple variations of countifs, has, contains, if........ I don't know.
any help would be greatly appreciated :)
Answers
-
Hi @AaronJones
This formula will hopefully deliver what you need based on your description and the formula you provided above.
=COUNTIFS({BMDA TAP QA Sheet Range 5}, CONTAINS("Completed", @cell),{BMDA TAP QA Sheet Range 1},"2023-02",{BMDA TAP QA Sheet Range 2},"John Doe")
This will only count where all 3 criteria are met.
Hope that helps
Thanks
Paul
-
i couldn't get this work either. I'm using cell references within my calc sheet. I changed the "contains" reference to verbatim, however, and it didn't seem to impact it:
=COUNTIFS({BMDA TAP QA Sheet Range 5}, CONTAINS("Completed",@cell),{BMDA TAP QA Sheet Range 1}, [Column3]55, {BMDA TAP QA Sheet Range 2}, [Column4]36)
any thoughts? thanks in advance!
-
Are you getting an error or an inaccurate count?
-
i'm getting unparseable with this one
-
I don't see any issues with the syntax you posted. Are you able to provide a snippet of the formula actually in the sheet similar to below?
-
Thanks for the assistance! I was able to get it to work with the formula below (which i swear i had already tried haha). The countifs previously wasn't returning what the countm function is for....however, with this combination it is somehow counting the elements appropriately.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!