Smartsheet CountIFs

kamnashah · 06/12/23

I have 2 SmartSheets. The first one (Sheet1) has customer data with some transactional information ('Customer Number', 'Order Number', 'Order Number', 'Licenses Used', etc.). The second one (Sheet2) has all transactional information ('Customer Number', 'Part Number', "Serial Number', 'Entitlement Complete (Y/N)', etc.).

All I am trying to do is ...

'Licenses Used" = Count of 'Entitlement Complete (Y/N)" where 'Customer Number' are same in Sheet1 and Shee2.

I am new to Smartsheet and am struggling. Any help / guidance is appreciated.

-Kamna

Carson Penticuff · 06/14/23

You are very close.

=COUNTIFS({Sheet BB Range 2}, "Yes", {Sheet BB Range 3}, [Customer Number]@row)

parulmishra · 06/13/23

@kamnashah

If my understanding is correct would you like Entitlement complete column to be specific value or just should have some value

You can try using something like this in the License Used column for Entitlement complete as "Y"

COUNTIFS({Entitlement Complete (Y/N)},"Y",[Customer Number]@row,{Customer Number})

You can try using something like this in the License Used column for Entitlement complete as not blank

COUNTIFS({Entitlement Complete (Y/N)},<>"",[Customer Number]@row,{Customer Number})

kamnashah · 06/13/23

Thank you for your response Parul. To provide you better details, the below are the two sheets ...

Essentially, the formula should show the value of 5 for Customer Number 836374 in "Sheet AA".

The formula I used is =COUNTIFS({Sheet BB Range 2}, "Yes", [Customer Number]@row, {Sheet BB Range 3})

Range 2 is column "Entitlement Complete" in Sheet BB and

Range 3 is "Customer Number" in Sheet BB.

However, for some reason, the formula returns "#INCORRECT ARGUEMENT SET". Wondering if you can assist / guide.

Carson Penticuff · 06/14/23

You are very close.

=COUNTIFS({Sheet BB Range 2}, "Yes", {Sheet BB Range 3}, [Customer Number]@row)

kamnashah · 06/22/23

Hello Carson,

That worked. Thank you so much for all your help.

kamnashah · 06/22/23

@kamnashah<\/a> <\/p>

If my understanding is correct would you like Entitlement complete column to be specific value or just should have some value <\/p>

You can try using something like this in the License Used column for Entitlement complete as "Y"<\/p>

COUNTIFS({Entitlement Complete (Y\/N)},"Y",[Customer Number]@row,{Customer Number})<\/p>

You can try using something like this in the License Used column for Entitlement complete as not blank<\/p>

COUNTIFS({Entitlement Complete (Y\/N)},<>"",[Customer Number]@row,{Customer Number})<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"kamnashah\",\"userID\":162315}}},{\"insert\":\" \\nIf my understanding is correct would you like Entitlement complete column to be specific value or just should have some value \\nYou can try using something like this in the License Used column for Entitlement complete as \\\"Y\\\"\\nCOUNTIFS({Entitlement Complete (Y\\\/N)},\\\"Y\\\",[Customer Number]@row,{Customer Number})\\nYou can try using something like this in the License Used column for Entitlement complete as not blank\\nCOUNTIFS({Entitlement Complete (Y\\\/N)},<>\\\"\\\",[Customer Number]@row,{Customer Number})\\n\"}]","format":"rich","dateInserted":"2023-06-13T10:14:09+00:00","insertUser":{"userID":151635,"name":"parulmishra","url":"https:\/\/community.smartsheet.com\/profile\/parulmishra","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/BGR3L9J9DSG7\/nGKBCUS5F71Q1.jpg","dateLastActive":"2023-08-04T11:13:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380254#Comment_380254","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/380254#Comment_380254

Thanks Parul that was very helpful

Smartsheet CountIFs

Best Answer

Answers

Help Article Resources

Categories