Is Adding COUNTIFS possible?

Eva M

I have a formula that looks at counting all instances where a certain unit in a hospital entered certain data into a master sheet in the last 7 days:

=COUNTIFS({K Cards Range 1}, "High fall risk", {K Cards Range 2}, "FALL", {K Cards Range 3}, "GMH", {K Cards Range 4}, =[Facility and Unit]30, {K Cards Range 5}, AND(@cell <= TODAY(), @cell > TODAY(-7)))

The above formula is searching through one column of data to find "High fall risk" and count how many times it finds it in that column based on the other criteria in the formula. It works perfectly.

Now, I need to create a formula that would do the above but look in three different columns for the same: "High fall risk".

I tried add the COUNTIFS, using "+" This returns back some data but not all of it.

=((COUNTIFS({K Cards Range 1}, "High fall risk", {K Cards Range 2}, "FALL", {K Cards Range 3}, "GMH", {K Cards Range 4}, =[Facility and Unit]30, {K Cards Range 5}, AND(@cell <= TODAY(), @cell > TODAY(-7))) + (COUNTIFS({K Cards Range 1}, "High fall risk", {K Cards Range 2}, "FALL", {K Cards Range 3}, "GMH", {K Cards Range 4}, =[Facility and Unit]30, {K Cards Range 5}, AND(@cell <= TODAY(), @cell > TODAY(-7)))) + (COUNTIFS({K Cards Range 1}, "High fall risk", {K Cards Range 2}, "FALL", {K Cards Range 3}, "GMH", {K Cards Range 4}, =[Facility and Unit]30, {K Cards Range 5}, AND(@cell <= TODAY(), @cell > TODAY(-7))))))

I tried using SUM of the COUNTIFS, but this one just returns zero:

=SUM(COUNTIFS({K Cards Range 1}, "High fall risk", {K Cards Range 2}, "FALL", {K Cards Range 3}, "GMH", {K Cards Range 4}, =[Facility and Unit]30, {K Cards Range 5}, AND(@cell <= TODAY(), @cell > TODAY(-7))), COUNTIFS({K Cards Range 1}, "High fall risk", {K Cards Range 2}, "FALL", {K Cards Range 3}, "GMH", {K Cards Range 4}, =[Facility and Unit]30, {K Cards Range 5}, AND(@cell <= TODAY(), @cell > TODAY(-7))), COUNTIFS({K Cards Range 1}, "High fall risk", {Upstate Facilities Market K Cards Range 2}, "FALL", {Upstate Facilities Market K Cards Range 3}, "GMH", {Upstate Facilities Market K Cards Range 4}, =[Facility and Unit]30, {Upstate Facilities Market K Cards Range 5}, AND(@cell <= TODAY(), @cell > TODAY(-7))))

Is it even possible to add several COUNTIFS? This is a fairly complicated report and it would be great if it worked. Otherwise, the only way I could possibly do it, is create a sheet with three columns, each using the first formula but looking in a different column on the original report, and then adding a fourth column, which would add the three columns.

Thank you!!

L_123

you had the correct idea with the +. You don't need the extra parenthesis however, just treat them as separate entities

=COUNTIFS({K Cards Range 1}, "High fall risk", {K Cards Range 2}, "FALL", {K Cards Range 3}, "GMH", {K Cards Range 4}, =[Facility and Unit]30, {K Cards Range 5}, AND(@cell <= TODAY(), @cell > TODAY(-7))) + COUNTIFS({K Cards Range 1}, "High fall risk", {K Cards Range 2}, "FALL", {K Cards Range 3}, "GMH", {K Cards Range 4}, =[Facility and Unit]30, {K Cards Range 5}, AND(@cell <= TODAY(), @cell > TODAY(-7))) + COUNTIFS({K Cards Range 1}, "High fall risk", {K Cards Range 2}, "FALL", {K Cards Range 3}, "GMH", {K Cards Range 4}, =[Facility and Unit]30, {K Cards Range 5}, AND(@cell <= TODAY(), @cell > TODAY(-7)))

I just used the same countifs formula 3 times, you need to change the references to fit the columns you want to count.

*side note, you should really name your other sheet references. I know it is easy to not do in the moment and just remember the number of the reference associated with the column, but if you ever have to come back and edit it, or if someone else is trying to fix/edit/understand your references it can become a real pain to work with if you don't name them.

L_123

you had the correct idea with the +. You don't need the extra parenthesis however, just treat them as separate entities

=COUNTIFS({K Cards Range 1}, "High fall risk", {K Cards Range 2}, "FALL", {K Cards Range 3}, "GMH", {K Cards Range 4}, =[Facility and Unit]30, {K Cards Range 5}, AND(@cell <= TODAY(), @cell > TODAY(-7))) + COUNTIFS({K Cards Range 1}, "High fall risk", {K Cards Range 2}, "FALL", {K Cards Range 3}, "GMH", {K Cards Range 4}, =[Facility and Unit]30, {K Cards Range 5}, AND(@cell <= TODAY(), @cell > TODAY(-7))) + COUNTIFS({K Cards Range 1}, "High fall risk", {K Cards Range 2}, "FALL", {K Cards Range 3}, "GMH", {K Cards Range 4}, =[Facility and Unit]30, {K Cards Range 5}, AND(@cell <= TODAY(), @cell > TODAY(-7)))

I just used the same countifs formula 3 times, you need to change the references to fit the columns you want to count.

*side note, you should really name your other sheet references. I know it is easy to not do in the moment and just remember the number of the reference associated with the column, but if you ever have to come back and edit it, or if someone else is trying to fix/edit/understand your references it can become a real pain to work with if you don't name them.

Eva M

OK, thank you so much!!!