COUNTIF (Counting a column based on other column Information)

Ryan Jared

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?

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?

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

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.