Smartsheet CountIFs
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
Best Answer
-
You are very close.
=COUNTIFS({Sheet BB Range 2}, "Yes", {Sheet BB Range 3}, [Customer Number]@row)
Answers
-
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})
Parul Mishra
-
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.
-
You are very close.
=COUNTIFS({Sheet BB Range 2}, "Yes", {Sheet BB Range 3}, [Customer Number]@row)
-
Hello Carson,
That worked. Thank you so much for all your help.
-
Thanks Parul that was very helpful
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!