CPR training tracker ( Countifs )

Thanks for the chance to pick your brain for a bit. I have been tasked with creating a sheet that helps track employees trained in CPR, their expirations, and a ratio of total employees PER SHIFT to CPR certified employees PER SHIFT.

I figured =COUNTIFS would be my best bet but I have ramming my head into a wall trying to figure it out. Hopefully it is something small ( I am new to excell's formulas) My formula is as follows: =COUNTIFS(Table4[Shift],"='CPR Tracker'!H1",'CPR Tracker'!K:K,">=3")

Capture.PNG

Any assistance would be greatly appreciated, also if you see a quicker easier way to do this... and it's no extra trouble, please share!

Thank you!

It's best to attach a sample worksheet (please see the yellow banner at the top of the page). We cannot see what's on the other sheets.

Not looking to count the cells differently. I am looking to count if BOTH criteria are met.

Person wants to see the ratio for shift "TEST", select shift "test" in drop down cell H1.
If E:E contains the same text that's in H1 ( the shift we want to see the ratio for ) AND the employee is not expired ( K:K >=3 ) then count.

Hopefully that sheds some light on my issue.

Rm,

Thanks for workbook and explanation.

You need to include Col K in the Table.

Click any cell in Table. Select TABLE DESIGN tab. Click RESIZE TABLE. Set it to include Col K (=$B$3:$K$14). Name Column K 'EXPIRED'.

Formula now becomes

=COUNTIFS(Table1[Shift],H1,Table1[Expired],">=3")

Ochimus

(Sorry - when I replied I didn't see that there was another reply already)

This should work. BTW, why don't you have column K in the table?

=COUNTIFS(Table1[Shift],$H$1,K4:K14,">=3")

I kept it separate as, to me, its really un-needed. I only have it to assist with the alerts and conditional formatting. I can still hide it in the blue border.

So I added the new column for the days to exp, added the new countifs formula. now it's kicking back a VALUE error that I cant figure out.

=COUNTIFS(Table4[Shift],"='CPR Tracker'!H1",Table2[Days to Exp],">=3") (NEW FORMULA)

=COUNTIFS(Table4[Shift],'CPR Tracker'!$H$1,Table2[Days to Exp],">=3") (tried this formula as well)

Updated the new column and formula the the attached reply.

I really appreciate all your help!

Your formula's don't match the file you attached. There is no Table4 and there is no header called "Days to Exp" in Table 2.
I think your second formula should work as long as it's referencing everything correctly.

RN,

Putting Col K in a second Table won't work unless you link them

Your formula will count every value in Col K that is three or more, whether or not the value in Table 4 is different to H1.

If you don't want to add Col K to the Table, just use Column references. Formula can then count how many rows meet both criteria.

Ochimus

GOT IT!

Something small and stupid i was doing + the help of all y'all!!!!

(WORKING FORMULA) =COUNTIFS(Table4[Shift],'CPR Tracker'!H1,Table4[Days to exp],">=3")

I kept saying =COUNTIFS(Table4[Shift],"='CPR Tracker'!H1",Table4[Days to exp],">=3") ** The extra "= at the beginning and extra " at the end killed it.

Thank you all for your input and time! Happy Holidays!!