Options
✭✭
edited 05/13/20

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