countifs

Adnan Khan

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

Paul Newcome

Try this one...

=SUMIFS({CA}, {C}, "LA", {CRTDATE}, >DATE(2021, 1, 2), {VPAID}, OR(@cell = "NO", @cell = ""))

Adnan Khan

https://community.smartsheet.com/discussion/75784/countifs

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))

Paul Newcome

Your first formula will need a criteria for the first range.

For your second: How is the data in the {cdate} range populated?

Adnan Khan

https://community.smartsheet.com/discussion/comment/278396#Comment_278396

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.

Paul Newcome

=COUNTIFS({cstatus}, "Closed", {cdate}, YEAR(DATEONLY(@cell)), = 2021)

Give that a go.

Adnan Khan

https://community.smartsheet.com/discussion/comment/278440#Comment_278440

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 ?

Paul Newcome

My apologies. I accidentally added a comma where it shouldn't be.

=COUNTIFS({cstatus}, "Closed", {cdate}, YEAR(DATEONLY(@cell)) = 2021)

Adnan Khan

https://community.smartsheet.com/discussion/comment/278483#Comment_278483

ops.. no problem.

It is working but it counting all from 2017 to 2021. weird.

Paul Newcome

Exactly how are those cells being populated?

Adnan Khan

https://community.smartsheet.com/discussion/comment/278494#Comment_278494

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", " ")

Adnan Khan

https://community.smartsheet.com/discussion/comment/278494#Comment_278494

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", " ")

Paul Newcome

Try this one...

=SUMIFS({CA}, {C}, "LA", {CRTDATE}, >DATE(2021, 1, 2), {VPAID}, OR(@cell = "NO", @cell = ""))

Adnan Khan

https://community.smartsheet.com/discussion/comment/278810#Comment_278810

Thanks Paul, you are awesome! 😉

Paul Newcome

Happy to help. 👍️

Adnan Khan

https://community.smartsheet.com/discussion/comment/278901#Comment_278901

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,

Paul Newcome

@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.