Thx for everyones help

Provided food for thought.

I managed a workaround

=COUNTIF(A1:Z1,"1") counts all 1's
=COUNTIF(A1:Z1,"0") counts all 0's

Then I Sum the results of both.

Here's another brainbuster for everyone:

Let's say I have A1:A4

Now if all cells in this range have values then I want to divide the sum of
this range by 4 = SUMIF(A1:A40)/4 to get the average of the 4 cells.

Now heres the tricky bit.

Lets say only 1, 2 or 3 of the cells have a value, how can I structure the
formula to evaluate the overall cell range to sum the cells then divide it
by the number of cells that actually have values to gain the average.

eg

= SUMIF(A1:A40)/3, then = SUMIF(A1:A40)/2 or = SUMIF(A1:A40)/1

To explain:

I have a % matrix that calculates if a certain time frame has been met on a
given day.

Lets use Monday, which has 4 trips calculated, so if all trips are done on
that day then I would average the % over the 4 trips. But if only 3 trips
are taken, then / by 3 trips.

The problem is I can't use a pre-designed formula of /4 if only 3 trips or
less are calculated, it will give me the wrong calculation.

Looking forward to everyones thought & ideas.

TIA
Regards
Mark.