Roster calculation using IFAND accross 2 tabs, need my formula simplified maybe a Vlookup?

[KTH23]

Hi I am trying to calculate something based on a roster, so I want it to be able to COUNT how many people "on" shift that day have each certificate (Cert 1-10) by looking this up on the 2nd tab.

My method is extremely long winded and checks person by person so wondered if anyone can assist with a shorter method, maybe introducing a vlookup?

Tab 1 is the Roster which goes day by day, and each person is marked with 'On' or blank for that day. If they are 'ON' I want to it to count how many of them have cert 1, cert 2, cert 3 etc....

So for those that are "on" for the first date of 25/5/16 that have a cert 1 my calculation is this:
=IF(AND(B5="On",Certs!$B$2="X"),1,0)+IF(AND(B6="On",Certs!$B$3="X"),1,0)+IF(AND(B7="On",Certs!$B$4="X"),1,0)+IF(AND(B8="On",Certs!$B$5="X"),1,0)+IF(AND(B9="On",Certs!$B$6="X"),1,0)+IF(AND(B10="On",Certs!$B$7="X"),1,0)+IF(AND(B11="On",Certs!$B$8="X"),1,0)+IF(AND(B12="On",Certs!$B$9="X"),1,0)+IF(AND(B13="On",Certs!$B$10="X"),1,0)+IF(AND(B14="On",Certs!$B$11="X"),1,0)

and for those that are 'on' for this same date but have a cert 2 is:
=IF(AND(B5="On",Certs!$C$2="X"),1,0)+IF(AND(B6="On",Certs!$C$3="X"),1,0)+IF(AND(B7="On",Certs!$C$4="X"),1,0)+IF(AND(B8="On",Certs!$C$5="X"),1,0)+IF(AND(B9="On",Certs!$C$6="X"),1,0)+IF(AND(B10="On",Certs!$C$7="X"),1,0)+IF(AND(B11="On",Certs!$C$8="X"),1,0)+IF(AND(B12="On",Certs!$C$9="X"),1,0)+IF(AND(B13="On",Certs!$C$10="X"),1,0)+IF(AND(B14="On",Certs!$C$11="X"),1,0)

As you can see these are checking person by person, would be much simpler to do maybe a vlookup Person 1 if "on" and sum the number of 'x' on the cert page?

I have uploaded an example of what i'm trying to calculate at the bottom each certificate for each day.

Hopefully someone can help!!

[samba_ravi]

B17

Please Login or Register  to view this content.

Try this and copy across

[KTH23]

Worked Perfectly! Thank you

[samba_ravi]

You are welcome and thanks for your feedback