Help with a Countif formula

[rwwall]

Hello,
I am trying to automate my workbook so that it will count all of a commercial drivers violations so that we can get an accurate Fatigue/form and manner violation rate for the company I work for.

The attached workbook has two sheet tabs (Jan and Driver summary).

I am able to get the occurrences to add from the Jan sheet to the summary sheet, however, it is only counting a single occurrence. I need assistance to figure out what formula is needed to count the "1's" from the January tab for that specific violation. It needs to count the 1's across only.

Located on the summary tab there are two sets of formulae. One counts the number of Missing, Major and F&M (yearly total). The other calculation is the specific type of violation which will be used in the FMVR.

The current formula is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&{"January"}&"'!F5:F33"),"*Violation name type*"))

 

[p45cal]

H5:

=SUM(FILTER(January!$O$5:$AS$34,ISNUMBER(FIND("7 Day Cycle violation",January!$F$5:$F$34)),0))

H6:

=SUM(FILTER(January!$O$5:$AS$34,ISNUMBER(FIND("13 Hour driving violation",January!$F$5:$F$34)),0))

H7:

=SUM(FILTER(January!$O$5:$AS$34,ISNUMBER(FIND("Missing record of duty status",January!$F$5:$F$34)),0))

H8:

=SUM(FILTER(January!$O$5:$AS$34,ISNUMBER(FIND("Driver Signature not recorded",January!$F$5:$F$34)),0))

Be aware it's case sensitive, so Hour is not the same as hour.
If you want it to be case insensitive, substitute FIND with SEARCH.

It'd be a bit more friendly if the January sheet wasn't password protected.

 

[rwwall]

Thank you!

I apologize forgot to remove the protection. So because I only added the Jan sheet for ease, to add the other sheets to account for the other months would the formula be:

=SUM(FILTER(January!,February!,March!,April!,May!,June!,July!,August!,September!,October!,November!,December!$O$5:$AS$34,ISNUMBER(FIND("Driver Signature not recorded",January!,February!,March!,April!,May!,June!,July!,August!,September!,October!,November!,December!$F$5:$F$34)),0))

I will check my spelling to ensure that any case sensitive words are correct.

 

[p45cal]

=SUM(FILTER(VSTACK(January:December!$O$5:$AS$34),ISNUMBER(SEARCH("7 Day Cycle violation",VSTACK(January:December!$F$5:$F$34))),0))

Make sure there are only the sheets you want to include in the calculation between the January and December sheets (no hidden sheets among them)

 

[rwwall]

=SUM(FILTER(VSTACK(January:December!$O$5:$AS$34),ISNUMBER(SEARCH("7 Day Cycle violation",VSTACK(January:December!$F$5:$F$34))),0))

Make sure there are only the sheets you want to include in the calculation between the January and December sheets (no hidden sheets among them)

Thank you. The hidden sheets are at the end the workbook. the ones with the data validation information etc.

 

[rwwall]

Good afternoon, I apologize I have one more question.
How do I adjust for months that have less than 31 days. Currently the formula looks up an array from 05 to AS34 but the following have less days:
Feb - AP
Leap year - AQ
Apr, Jun, Sep and Nov - AR

I appreciate your assistance, thank you!

 

[p45cal]

How do I adjust for months that have less than 31 days.

Don't all the sheets have the same arrangement but the end of the month numbers are missing in row 4 when appropriate?
In those shorter months those columns wouldn't have anything in them.

 

[rwwall]

Unfortunately not. The shorter months will have run into the violation count and it will add those to the as well. That is what is throwing my numbers off. I am going to try and insert the columns and then hide them to see if that fixes the issue. I've attached the entire workbook.

 

[p45cal]

I see you've already got some summing formula in colums around BA on each sheet. Make sure that these fomulae are on the same column in each sheet then, lets say you decide on column BA, the formula on the summary sheet becomes:

=SUM(FILTER(VSTACK(January:December!$BA$5:$BA$34),ISNUMBER(SEARCH("7 Day Cycle violation",VSTACK(January:December!$F$5:$F$34))),0))