Derive a formula without calculating multiple COUNTIFS

think i've massively overcomplicated my problem here in that explanation... basically i need the sum of multiple rows of COUNIF values in order to account for the data in Jan through to Dec! (ie 12 rows), but i get an error when i try to manually choose the rows and divide it by the days worked COUNTIF...i get an error

One work around is i sum up the values from each month into a master tableat the top, but hoping theres a cleaner way with less moving parts!!

Thanks!

How about

Formula:

Please Login or Register  to view this content.

ok cool, thats working better...! but ideally i want this formula for the top in D2 & dD3, counting ALL the COUNTIFS for Jan, Feb, Mar.....etc in one formula

If i do the above formula individually for each moth, it's counting the wrong days (ie Feb starts on a Thursday, so doesn't count a whole 5day week..... so AG14 AND AG15 cells are incorrect (RED SHADED)

Therein lies the problem... i need to keep a running total of all days in one COUNTIF formula, if that's possible..?

The formula I supplied does exactly what you originally asked for & gives the same values as your formula, so not sure what you mean about Feb.

Thanks Fluff13, but I need it for the whole year, not just Jan... =COUNTIFS(B8:AF8,"O")/COUNTIFS(B8:AF8,"<>")*5 is just counting for Jan

If i try to edit this formula to include all the months, =COUNTIFS(B8:AF8,B13:AD13,B18:AF18,"O")/COUNTIFS(B8:AF8,B13:AD13,B18:AF18,"<>")*5 , i get an error:

This is running Jan to March end in this example above

For Feb you use

Formula:

Please Login or Register  to view this content.

Is there a formula that can sum all the months together, not break them down month by month

If that's what you want, why didn't you say so to start with?
Try

Formula:

Please Login or Register  to view this content.

Thanks, sorry, im super novice! appreciate your help
unfort. i get a #DIV/O! error

BUT THIS IS LOOKING WAYYYY MORE AWESOME!

Please attach a workbook that shows the problem.

Attached!
Thanks vm!

Thanks for that, how about

Formula:

Please Login or Register  to view this content.

WOW. NAILED IT! Looking great!!!
I'm amazed how there is always a solution to a problem here. Thanks so much Fluff13, legend.

Glad to help & thanks for the feedback.

One last thing if i i may.... id also like to be able to include 3 more variables:

We currently have:

WFO: O
WFH: H

Can i add

Public Holiday: PH
Leave: L
Sick Day: S

These days shouldn't count towards lowering the average, but id like to be able to add them in the rows if and when necessary. Currently adding any additional letters takes it as WFH (H)
(I guess this is going back to using COUNTIFS?! Hope not... the solution you provided above is so beautiful!

What should the value in D2 be?

Hi Fluff13, I'm just going to use colour fills for these values, so any data doesn't interfere with the value in D2.
What you provided works beautifully!

Thanks again, marking as SOLVED!

Glad to help & thanks for the feedback.