countifs
Hello guys,
I m new into smart sheet. I am trying to use counif function but it gives me always error. I do not know why.
=COUNTIFS({Transport Claims Tracker Range 1}, {Transport Claims Tracker Range 2}, "LA")
Am I doing any mistake ?
I always get this message #INCORRECT ARGUMENT SET
Best Answer
-
Try this one...
=SUMIFS({CA}, {C}, "LA", {CRTDATE}, >DATE(2021, 1, 2), {VPAID}, OR(@cell = "NO", @cell = ""))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
I have one more question.
I have claim status column and created date column . I want to count if claim status "closed" and date> 12/31/2020. I am using below formula but is giving me sum of all from 2019 to 2021 but I want to see what status closed in 2021 only.
=COUNTIFS({cstatus}, "Closed", {cdate}, >DATE(2020, 12, 31))
-
Your first formula will need a criteria for the first range.
For your second: How is the data in the {cdate} range populated?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul,
first formula.
I fixed this problem. Thanks.
=COUNTIFS({Transport Claims Tracker Range 2}, "LA")
2nd formula.
cdate format is as below, it is date field.
-
=COUNTIFS({cstatus}, "Closed", {cdate}, YEAR(DATEONLY(@cell)), = 2021)
Give that a go.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul,
I tried but it gave me this error. #INVALID OPERATION
what @cell represent. I selected cdate as entire column. Could it be reason ?
-
My apologies. I accidentally added a comma where it shouldn't be.
=COUNTIFS({cstatus}, "Closed", {cdate}, YEAR(DATEONLY(@cell)) = 2021)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
ops.. no problem.
It is working but it counting all from 2017 to 2021. weird.
-
Exactly how are those cells being populated?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul, it is working. Sorry my bad. I was putting wrong year. Thank you.
Do you know if I have put two condition on same cell , on column" vpad" it should pickup blank and "No". I used this formula but not working. kindly take a look. my first 2 conditions are fine but last one i do not know.
Thanks in advance.
=SUMIFS({CA}, {C}, "LA", {CRTDATE}, >DATE(2021, 1, 2), {VPAID}, ("NO", " ")
-
Hi Paul, it is working now. Sorry my bad, I was putting wrong year. I was blind - lol
I have one more if we two condition on same cell. Vpaid is column i need to pickup blank and "No" both. my first 2 condition are fine but last one, may be syntax error.
Thanks in advance.
=SUMIFS({CA}, {C}, "LA", {CRTDATE}, >DATE(2021, 1, 2), {VPAID}, ("NO", " ")
-
Try this one...
=SUMIFS({CA}, {C}, "LA", {CRTDATE}, >DATE(2021, 1, 2), {VPAID}, OR(@cell = "NO", @cell = ""))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul, you are awesome! 😉
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul, Sorry to bug you again.
I am creating a report. There is list of carriers in source sheet. I want to bring distinct carrier name into my report. I checked it is 126 in total. what I did I just copied and past distinct carrier but If someone add new it wont show I believe.
How can I bring in my report. If someone add a new carrier then it will add to my report in real time.
Thanks,
-
@Adnan Khan I would suggest creating a separate thread for this question as it does not pertain to the COUNTIFS function. You are welcome to tag me in your post, and I will be happy to try to help with a solution there.
That way everything is more easily searchable by others using the Community.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!