COUNTIF and HAS calculations

Cayla Davis

I am trying to get metrics where if 'NDC' is found on another sheet, and it has Under Review,
Pending Development or Pending Production Release, then it will calculate the total of NDC items that i have. I tried this command, but it's not working and i dont know how else to achieve it.

=COUNTIF({Sabre Traveler Care Priorities Range 5}, "NDC" HAS({Sabre Traveler Care Priorities Range 1}, [Primary Column]2:[Primary Column]4)

AKnight

Oh man, stupid mistake of over parathesis-ing on my part from copying and pasting. Remove the parenthesis that comes before the last two HAS, like:

=COUNTIFS({Sabre Traveler Care Priorities Range 5}, HAS(@cell, "NDC"), {Sabre Traveler Care Priorities Range 4}, OR(HAS(@cell, [Primary Column]2), HAS(@cell, [Primary Column]3), HAS(@cell, [Primary Column]4)))

Gwendolene Day

Do you want rows 1-3 in the NDC column of your matrix pictured to have all NDCs for all 3 statuses (therefore all counts would be the same)? Or should NDC1 have NDC in "Under Review", NDC2 is NDC is Pending Development, NDC3 is Pending Production Release? Does Range 5 include NDC alone or within the cell?

If Range 5 has cells with only NDC and the Range 1 is a single select dropdown, maybe try:

=COUNTIFS({Range 5},"NDC",{Range 1}, Primary@row) - then make it a column formula

If Range 5 includes other text and you want to count any cell that contains NDC, and/or Range 1 is a multi-select dropdown, you could use CONTAINS in one or both criteria:

=COUNTIFS(CONTAINS({Range 5},"NDC"),CONTAINS({Range 1},"Under Review")) (replace "Under Review" with the other statuses for rows 2-3.

Cayla Davis

Thanks for responding, i want to count if NDC is found on any open items (Under Review, Pending Development, Pending Production Release) to then display the number - i just tried the aboves and it doesnt seem to be working

AKnight

Hey @Cayla Davis !

Couple things, I would change your COUNTIF to COUNTIFS to allow for multiple if statements, specifically allowing you to break up your "if 'NDC' is found on another sheet" and "has Under Review, Pending Development or Pending Production Release" statements.

Then for your HAS statement (this assumes the {Sabre Traveler Care Priorities Range 1} is a dropdown/ multidrop down column, if not use CONTAINS instead), you will have to specify in that range that the cell will contain one of your options. Due to there being multiple options to select from, you will need to add OR prior to your has (Under Review, Pending Development or Pending Production Release.

Your formula should look like:

=COUNTIFS({Sabre Traveler Care Priorities Range 5}, "NDC", {Sabre Traveler Care Priorities Range 1}, OR(HAS(@cell, [Primary Column]2), (HAS(@cell, [Primary Column]3), (HAS(@cell, [Primary Column]4)))

Cayla Davis

Thanks Ashley! I appreciate all the extra detail, i've been working hard on getting an understanding on these formulas, so that helped. I tried what you suggested but its coming back as UNPARSEABLE

Cayla Davis

Thank you @AKnight! I appreciate you taking the time to give more detail, i am still learning some of the formulas so your explanation really helped. However, i still cannot get it to work, not sure what i am doing wrong.

I noticed my range was wrong on one so i updated that so now it looks like

=COUNTIFS({Sabre Traveler Care Priorities Range 5}, "NDC", {Sabre Traveler Care Priorities Range 1}, OR(HAS(@cell, [Primary Column]2), (HAS(@cell, [Primary Column]3), (HAS(@cell, [Primary Column]4)))

Priorities Range 5 - is where to find my NDC content
Priorities Range 1 - is where to find the Under Review, Pending Development etc… do i need Range 1 if im highlighting them already on my HAS statements?

AKnight

Hi @Cayla Davis !

Yep! You will need to specify Range 1 so the formula knows which cells to call within your HAS statement. Imagine giving someone a neighborhood to look at (sheet), the range specifies the street (column), and the cell mention specifies the house (@cell), the has statement says if the house is red count (if cell contains X status).

I'll need more info because there might be an additional statement we have to add,

Priorities Range 5 - is it an open text (as in someone is typing "NDC"), a dropdown, or is NDC mentioned somewhere in a sentence?

Priorities Range 1 - is this also an open text or is it a dropdown/multidrop-down?

edit to add because I realized having "NDC" with no @cell might be confusing, you can add an @cell = statement, but it will effectively be doing the same thing as just having "NDC". COUNT statements (COUNTIF(S) AND SUMIF(S)) need a range and HAS needs an additional search range. So we can provide street (column) for COUNT/SUM and house (cell) for HAS.

Cayla Davis

Thanks @AKnight, appreciate you helping me with this! sorry for the delay in responding, been a crazy day

Priorities Range 5 - it is a dropdown of just NDC or Graphical , only allowing 1 entry
Priorities Range 4 - this is a dropdown as well, only allowing 1 entry

With that information, would it change what the formula would be? This is what i have right now

=COUNTIFS({Sabre Traveler Care Priorities Range 5}, "NDC", {Sabre Traveler Care Priorities Range 4}, OR(HAS(@cell, [Primary Column]2), (HAS(@cell, [Primary Column]3), (HAS(@cell, [Primary Column]4)))

AKnight

Hey @Cayla Davis !

Because both are dropdowns I would add HAS with @cell for the NDC section, try:

=COUNTIFS({Sabre Traveler Care Priorities Range 5}, HAS(@cell, "NDC"), {Sabre Traveler Care Priorities Range 4}, OR(HAS(@cell, [Primary Column]2), (HAS(@cell, [Primary Column]3), (HAS(@cell, [Primary Column]4)))

Let me know if that returns any errors or doesn't calculate the correct amount!

Cayla Davis

Darn! didnt work, just keeps coming back UNPARSED

AKnight

Oh man, stupid mistake of over parathesis-ing on my part from copying and pasting. Remove the parenthesis that comes before the last two HAS, like:

=COUNTIFS({Sabre Traveler Care Priorities Range 5}, HAS(@cell, "NDC"), {Sabre Traveler Care Priorities Range 4}, OR(HAS(@cell, [Primary Column]2), HAS(@cell, [Primary Column]3), HAS(@cell, [Primary Column]4)))

Cayla Davis

You are an angel in disguise! IT WORKED!!! YAY!!! and now i know how to do this for some more automation i am looking at. Could you run this same thing by instead of looking at a word, could you look at it by Creation Date, so if you wanted everything from August, could you do that?

You're the best! THANK YOU THANK YOU THANK YOU!

AKnight

@Cayla Davis Yep! You can use creation date, modified date, create by, or really any column type in formulas and automations like this.