Formula with using three separate columns

Jolene Luna

I am trying to write a formula that counts the number of PINS Case_FRB based on Force Level_FRB and within a specific date range. I'm using the formula below, but it is not working.

=COUNTIFS([Date Presented to FRB]1:[Date Presented to FRB]205, AND(@cell >= DATE(2021, 2, 1), @cell <= DATE(2021, 5, 5)), [Force Level_FRB]1:[Force Level_FRB]205, @cell = "SUoF / L3"), [PINS Case_FRB]:[PINS Case_FRB], AND(@cell = "PINS"))

I am also trying to write a formula using a begins with statement. I would like to count all case numbers that begin with "19-".

I attempted the formula below but it returned a #unparseable error.

=COUNTIFS([Force Level_FRB]:[Force Level_FRB], "Tactical" AND [Case Number]:[Case Number], or(@cell = "19-*"))

Help is appreciated!

Jen Lange

Hi @Jolene Luna.

1) I think there are some syntax issues in your COUNTIFS formula, but it's hard to troubleshoot without access to your sheet. Try the below version instead and let me know if it works.

=COUNTIFS([Date Presented to FRB]1:[Date Presented to FRB]205, AND(@cell >= DATE(2021, 2, 1), @cell <= DATE(2021, 5, 5)), [Force Level_FRB]1:[Force Level_FRB]205, @cell = "SUoF / L3", [PINS Case_FRB]:[PINS Case_FRB], @cell = "PINS")

2) I cannot follow the objective of your criterion1. Are you looking for "Tactical" within the Case Number range OR for the cell to begin with "19-"?

Please clarify the criterion on that second COUNTIFS formula and I can help you to revise the statement. I'll likely use CONTAINS("19-",[range]), but I need to understand the objective and then better advise.

-Jen

Jolene Luna

The formula below did not work. I am trying to get a count on the number of "SUoF / L3" cases from the column titled "Force Level_FRB" that are identified as a "PINS" case based on the column title "PINS Case_FRB" where the cell is populated with the word "PINS", using a date specified date range from the column "Date Presented to FRB".

1) I think there are some syntax issues in your COUNTIFS formula, but it's hard to troubleshoot without access to your sheet. Try the below version instead and let me know if it works.

=COUNTIFS([Date Presented to FRB]1:[Date Presented to FRB]205, AND(@cell >= DATE(2021, 2, 1), @cell <= DATE(2021, 5, 5)), [Force Level_FRB]1:[Force Level_FRB]205, @cell = "SUoF / L3", [PINS Case_FRB]:[PINS Case_FRB], @cell = "PINS")

In response to your question below, I am looking for the number of "Tactical" cases from column "Force Level_FRB" that begin with a "19-" case number from the column "Case Number". I am looking for the cell to begin with "19-" in the Case Number column. Thank you!

2) I cannot follow the objective of your criterion1. Are you looking for "Tactical" within the Case Number range OR for the cell to begin with "19-"?

Please clarify the criterion on that second COUNTIFS formula and I can help you to revise the statement. I'll likely use CONTAINS("19-",[range]), but I need to understand the objective and then better advise.

Jen Lange

Okay. Thanks for the feedback, Jolene. Let me try to rework the formula today.