Urgent help needed with formula to count criteris from various worksheets in one workbook

Dear All



I was wondering whether someone can help with the following problem:

I have a rota workbook. which is divided into monthly tabs. I have attached a simple version of this for your reference.

I need a formula or VB code (whichever is easier) to do the following:

In Sept17 sheet I need to look at all the names from previous sheets (Aug17,Jul17,Jun17,May17,Apr17 and Marc17) and at the end of the each row for the names (for name a cells AP14 and AQ14 ) to count the number of times that person has worked on Saturdays (Ap14 = Count of Saturdays worked in previous months and including Saturdays incurrent sheet) and Sundays(AQ14 = Count of Sundays worked in previous months and including Sundays in current sheet). Basically for name A in Sept 17 sheet it counts all the Saturdays which was worked (row 7 has the name of the weeks) from all previous sheets for name A + Saturdays worked in Sep17 and add them and place it on AP14.

Days worked are if Col B to AC and should only count those which are = AD,IN,E,L,E/B,L/UB.

I really hope I am clear in what I am trying to achieve. Any urgent help is appreciate it

Try

Saturdays (in each sheet as you already calculate these in some sheets)

=SUM(COUNTIFS($B$7:$AC$7,"Sat",$B14:$AC14,{"AD","IN","E","L","E/B","L/UB"}))

and Sundays

=SUM(COUNTIFS($B$7:$AC$7,"Sun",$B14:$AC14,{"AD","IN","E","L","E/B","L/UB"}))

If ALL the data is in consistent rows and columns in each sheet (which it is NOT currently) you can use

=SUM(MAR17:SEP17,AI14) to give Saturdays for "a"

Dear Richard

Thank you for you help and advice, I completely agree with you , however I was given this spreadsheet and had to do this quickly hence my question. Thanks for the help .

Dear John, thank you for you solution I have to make the sheets consistent and your solution work, I was hoping for a quick fix but unfortunately your suggestion seems to work.

Thanks again for all your helps

Interesting response ...

unfortunately your suggestion seems to work. ....

Sorry I was typing this quickly , I didn't mean anything bad by this, basically you solution does work and thank you , the unfortunate bit is I have to change the rows and columns to make everything consistent:-)

I am very grateful for all the help I have had in this forum:-)

... My reply was "tongue in cheek": I did understand that my solution involved you in a little more work but offered a simple solution, given you were already doing the calculations on the monthly sheets