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?

Find more posts tagged with

Formulas

Accepted answers

Ryan Jared

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

All comments

David Tutwiler

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

Ryan Jared

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?

David Tutwiler

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.

Ryan Jared

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.

David Tutwiler

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.

Ryan Jared

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

David Tutwiler

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.

Ryan Jared

David Tutwiler

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

Ryan Jared

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

David Tutwiler

Awesome. Glad we go it going.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.