Help with CountIf

Tabitha W.

Hi Everyone,

I am new to formulas and have been struggling with a CountIf (I'm sure this won't be my last questions).

I've watched videos and looked at past logs but I still can't wrap my brain around this simple request.

I am trying to count the number of instances where a specific location has an open request that is notated by a check box. In this case, I want to count where the "open requests" are thus the checkbox would not be selected. I am pulling from a reference sheet into my roll-up sheet.

Here is the formula I have (that is incorrect)

=COUNTIFS({On the Job Injury Log Range 9}, "Baltimore City Correctional Ce", {On the Job Injury Log Range 11}, 0)

Any help in figuring out where I went wrong would be appreciated.

Thanks!

Tabitha

Paul Newcome

Yes. THat first one should do the trick.

As for the <= 60 bit...

You would use either a COUNTIFS to repeat the range and do your criteria of >= 1 for the first range and<= 60 for the second repetition of the range OR you could use a COUNTIF with the AND. It is your choice. I personally always use COUNTIFS even if I only have a single range.

=COUNTIFS({On the Job Injury Log Range 10}, >=1, {On the Job Injury Log Range 10}, <=60)

or

=COUNTIF({On the Job Injury Log Range 10}, AND(@cell >= 1, @cell <= 60))

or my personal preference

=COUNTIFS({On the Job Injury Log Range 10}, AND(@cell >= 1, @cell <= 60))

Paul Newcome

What are you getting? Are you getting an incorrect count or an error? What columns are your cross sheet references pointing at?

Tabitha W.

Hi Paul!

Trial, error and lots of community reading and I got it to work-

=COUNTIFS({On the Job Injury Log Range 9}, "Baltimore City Correctional Ce", {On the Job Injury Log Range 11}, 0)

Now I'm stuck on a formula for <=60

I get the error of incorrect argument

=COUNTIF({On the Job Injury Log Range 10}, >=1, (AND({On the Job Injury Log Range 10}, <=60)))

Tabitha W.

I think I figured this out too- I needed it to be =CountIfs NOT =CountIf

Paul Newcome

Yes. THat first one should do the trick.

As for the <= 60 bit...

You would use either a COUNTIFS to repeat the range and do your criteria of >= 1 for the first range and<= 60 for the second repetition of the range OR you could use a COUNTIF with the AND. It is your choice. I personally always use COUNTIFS even if I only have a single range.

=COUNTIFS({On the Job Injury Log Range 10}, >=1, {On the Job Injury Log Range 10}, <=60)

or

=COUNTIF({On the Job Injury Log Range 10}, AND(@cell >= 1, @cell <= 60))

or my personal preference

=COUNTIFS({On the Job Injury Log Range 10}, AND(@cell >= 1, @cell <= 60))

Tabitha W.

Thanks for that tip!

I REALLY appreciate it!

Paul Newcome

Happy to help! 👍️