COUNTIF表示范围内的数字

西蒙-威尔科克
西蒙-威尔科克 ✭✭✭✭✭
在公式和函数

早....

我使用下面的公式来计算在列中找到特定值的次数，其中列出了许多不同的子句值，有时每个单元格中有多个值。

=COUNTIFS({Audit - Range 1}， FIND((电子邮件保护)， @cell) > 0)

例子

4．1

4.1.2

4．1

4.4, 4.8, 6.7

例如，我想计算值4.1在一个列中出现了多少次(参见上面)。这个公式可以工作，但也可以计算字符串中具有相同值的任何其他单元格，如上面所示。它计算4.13倍因为它也在4.1.2中，我想只计算4.1的值，所以返回2倍．

我希望你有某种独特的价值能力。

什么好主意吗?

谢谢

· 在Facebook上分享 在Twitter上分享

最佳答案

答案

帮助文章资源欧宝体育app官方888

在Smartsheet中创建和编辑公式

交叉表引用的公式组合

内容功能列表

类别

Hey @Lisa Matthews<\/a> <\/p>

Try this<\/p>

=COUNTIFS(ryg:ryg, \"Red\", [New Target Finish]:[New Target Finish], @cell<>\"//www.santa-greenland.com/community/discussion/91573/\")<\/p>

=COUNTIFS([New Target Finish]:[New Target Finish], @cell = \"//www.santa-greenland.com/community/discussion/91573/\", [Task Name]:[Task Name], @cell <> \"//www.santa-greenland.com/community/discussion/91573/\")<\/p>

For your empty Target Finish count I added your primary column to the formula as a filter - otherwise the formula would find the empty cells all the way down the sheet regardless if you were using the row or not.<\/p>

Do these work for you?<\/p>

Kelly<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-30 16:28:14","updateUser":{"userID":119439,"name":"Lisa Matthews","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20Matthews","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!GlFVn_Nz2Xw!wlDUP2yDueY!ag-sVUwuD_X","dateLastActive":"2022-10-30T16:28:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":97141,"type":"question","name":"COUNTIFS COUNTM to count the number of instances in multi-select columns, based on several criteria?","excerpt":"Hello, I have been trying to create a formula that would count the total number of instances in multi-select columns, based on criteria in cross-referenced sheets, but sadly with no success. To illustrate what I'm trying to do please see the example below: From my reference data in Sheet1 - I would like to receive a sum of…","categoryID":322,"dateInserted":"2022-10-28T23:36:06+00:00","dateUpdated":null,"dateLastComment":"2022-10-30T23:15:57+00:00","insertUserID":154299,"insertUser":{"userID":154299,"name":"MKa","url":"https:\/\/community.smartsheet.com\/profile\/MKa","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-30T23:14:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154299,"lastUser":{"userID":154299,"name":"MKa","url":"https:\/\/community.smartsheet.com\/profile\/MKa","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-30T23:14:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3334173723,"url":"https:\/\/community.smartsheet.com\/discussion\/97141\/countifs-countm-to-count-the-number-of-instances-in-multi-select-columns-based-on-several-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97141\/countifs-countm-to-count-the-number-of-instances-in-multi-select-columns-based-on-several-criteria","format":"Rich","lastPost":{"discussionID":97141,"commentID":349260,"name":"Re: COUNTIFS COUNTM to count the number of instances in multi-select columns, based on several criteria?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/349260#Comment_349260","dateInserted":"2022-10-30T23:15:57+00:00","insertUserID":154299,"insertUser":{"userID":154299,"name":"MKa","url":"https:\/\/community.smartsheet.com\/profile\/MKa","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-30T23:14:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/5NMLGF82700A\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-30T23:14:21+00:00","dateAnswered":"2022-10-30T16:13:37+00:00","acceptedAnswers":[{"commentID":349247,"body":"

Hey @MKa<\/a> <\/p>

Great job on finding a workaround. A working formula is a good formula in my book. In the future, in case you don't want to add the helper column to the source sheet, the syntax for filtering by the last 7 days of data is <\/p>

{your date column on source sheet}, AND(@cell >= TODAY(-7), @cell <= TODAY())<\/p>

because dates can sometimes be finnicky in formulas, I usually add an ISDATE() to weed out any non-date responses that might be in the column.<\/p>

{your date column on source sheet}, AND(ISDATE(@cell), @cell >= TODAY(-7), @cell <= TODAY())<\/p>

Excellent job on answering your own question. We look forward to hearing more questions from you- it helps us all learn something.<\/p>

Kelly<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-30 23:14:21","updateUser":{"userID":154299,"name":"MKa","url":"https:\/\/community.smartsheet.com\/profile\/MKa","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-30T23:14:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":97044,"type":"question","name":"Help combining: IF INDEX MATCH","excerpt":"Hi, Please help me with my formula, I'm trying to GET a Contact Column, based on matching criteria, (The columns referenced are from a separate sheet.) I tried using: =IF({Sheet 1 Range Status Column}, \"Approved\", INDEX({Sheet 1 Range Contact Column}, MATCH(HAS(@cell, Reason@row), {Sheet 1 Range Reason Column}, 0))) It's…","categoryID":322,"dateInserted":"2022-10-26T22:04:44+00:00","dateUpdated":null,"dateLastComment":"2022-10-30T20:05:39+00:00","insertUserID":123243,"insertUser":{"userID":123243,"name":"A Rose","title":"","url":"https:\/\/community.smartsheet.com\/profile\/A%20Rose","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-30T20:05:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":123243,"lastUser":{"userID":123243,"name":"A Rose","title":"","url":"https:\/\/community.smartsheet.com\/profile\/A%20Rose","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-30T20:05:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":46,"score":null,"hot":3333985823,"url":"https:\/\/community.smartsheet.com\/discussion\/97044\/help-combining-if-index-match","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97044\/help-combining-if-index-match","format":"Rich","tagIDs":[254,319],"lastPost":{"discussionID":97044,"commentID":349252,"name":"Re: Help combining: IF INDEX MATCH","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/349252#Comment_349252","dateInserted":"2022-10-30T20:05:39+00:00","insertUserID":123243,"insertUser":{"userID":123243,"name":"A Rose","title":"","url":"https:\/\/community.smartsheet.com\/profile\/A%20Rose","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-30T20:05:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-30T20:05:50+00:00","dateAnswered":"2022-10-28T08:30:26+00:00","acceptedAnswers":[{"commentID":349088,"body":"

Hi @A Rose<\/a> <\/p>

My apologies! There was meant to only be one formula, I believe I didn't copy\/paste it properly. Here's what I meant to suggest:<\/p>

=JOIN(COLLECT({Sheet 1 Range Contact<\/strong> Column}, {Sheet 1 Range Status<\/strong> Column}, \"Approved\", {Sheet 1 Range Reason<\/strong> Column}, HAS(@cell, Reason@row)), CHAR(10))<\/p>


<\/p>

This will then look at your Reason in the current row, then check the other sheet's Reason column. For any row where that same reason appears, it will bring together all the unique Contacts associated, as long as that row is also \"Approved\". <\/p>

Here's an example of a source sheet:<\/p>

\n
\n \n \"Screenshot<\/img><\/a>\n <\/div>\n<\/div>\n

Then the formula output:<\/p>

\n
\n \n \"Screenshot<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>

There isn't a direct way to then convert the multi-select options back into Contacts using a formula. <\/p>

Yes, you could use a VLOOKUP or INDEX(MATCH. In this instance you would need to have a reference sheet with all possible contact combinations<\/em>. For example, a multi-select column with each possible combination on the left, and a contact column with the Contact equivalent. <\/p>

In my image above, I have 3 contacts. This means the reference grid would have 7 rows\/options:<\/p>

Genevieve \/ Krissia \/ Mirka \/ Genevieve & Krissa \/ Genevieve & Mirka \/ Krissia & Mirka \/ Genevieve, Krissia, and Mirka<\/p>

Let me know if I can help clarify anything further! <\/p>

Cheers,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-30 20:05:50","updateUser":{"userID":123243,"name":"A Rose","title":"","url":"https:\/\/community.smartsheet.com\/profile\/A%20Rose","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-30T20:05:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":319,"urlcode":"functionality","name":"functionality"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">