COUNTIF (Counting a column based on other column Information)
Using 2 columns (Status) & (Tracking) I'm trying to count the amount of "complete" status in the Status column where the Tracking column is not Past Due. Below is the formula I created.
=COUNTIF(AND(Tracking:Tracking, <>"Past Due", Status:Status, "Complete"))
It's not taking the formula, any thoughts?
Bonus Question* ***
Eventually my plan is to due this based on information in a referenced sheet. Any ideas on if that's possible?
Best Answer

@David Tutwiler You're right, your formula is correct. Where it's going wrong for me is I don't have any other option in Tracking other than Past due. Is there away to have it take into count the blank cells?
Answers

I think it's just a syntax thing. You're trying to do a COUNTIFS with a COUNTIF and an AND. If you change it to this, it should work.
=COUNTIFS(Tracking:Tracking, <>"Past Due", Status:Status, "Complete")

Thanks David,
It didn't work, I did get a number value however so the formula took, but it's showing 0 as the count. Any idea?

Are you certain that you have data that matches the criteria? That there is a row where both the Tracking is not equal to Past Due and the Status is Complete?
Also, if you'll @mention my name I'll get notified when you respond. I just happened to be checking back on this one.

Hey @David Tutwiler
Yes, I have 14 "complete" statues in the Status Column and 3 "Past Due" in the Tracking column next to a complete status.

That might be your problem as your symbol <> is NOT equal to. If you're looking for when you have "Past Due" and "Complete" then you'll need to make it a = symbol.

@David Tutwiler Great suggestion, but I'm looking for the opposite of that. When I change it to an (=) it gave me the total count of instances where complete is also past due.
Im looking for the total count of cells that are complete and do not have a past due.

Hmm. I'm not sure. I'd really check the data closely. Here is my test sheet where I checked your formula against different data types. The first formula I posted should get you the answer.

@David Tutwiler You're right, your formula is correct. Where it's going wrong for me is I don't have any other option in Tracking other than Past due. Is there away to have it take into count the blank cells?

Oh ok. So if I understand right you want Status to be Complete and No Past Due (or blank)? If that's right then you could try:
=COUNTIFS(Tracking:Tracking, ISBLANK(@cell), Status:Status, "Complete")

@David Tutwiler That did the trick, thank you so much!

Awesome. Glad we go it going.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!