Sum Problem requiring help

[NoodNutt]

Hi all

What I am trying to achieve is to sum each cell in my range that has a value
either 0 or 1, but if a cell is "", then don't sum those cells.

=Sum($B$7:$B$10,$I$6,$I$8:$I$10,$P$6:$P$7,$P$9:$P$10,$W$6:$W$8,$W$10,$AD$6:$AD$9)

Tried this but am wrong

=Sum(Product($B$7:$B$10,$I$6,$I$8:$I$10,$P$6:$P$7,$P$9:$P$10,$W$6:$W$8,$W$10,$AD$6:$AD$9,<>0),"")

Need some guidance please.

TIA
Mark.

 

[willfeld]

If you just want to count how many cells in the range have a 0 or 1 just use
the COUNT function select the range and the target cell for the result and
your done.

 

[ryguy7272]

Not sure what you are doing, but maybe you can try something like this:
=COUNTA(A2:A10)-COUNTBLANK(A2:A10)
(just offered as an example...)

Regards,
Ryan--

 

[Spiky]

Hi all

What I am trying to achieve is to sum each cell in my range that has a value
either 0 or 1, but if a cell is "", then don't sum those cells.

=Sum($B$7:$B$10,$I$6,$I$8:$I$10,$P$6:$P$7,$P$9:$P$10,$W$6:$W$8,$W$10,$AD$6:$AD$9)

Tried this but am wrong

=Sum(Product($B$7:$B$10,$I$6,$I$8:$I$10,$P$6:$P$7,$P$9:$P$10,$W$6:$W$8,$W$10,$AD$6:$AD$9,<>0),"")

Need some guidance please.

TIA
Mark.

Ok, summing blank cells returns 0, so that doesn't matter. Summing 0
returns 0, so that doesn't matter. It sounds like all you really want
is to count the number of 1s that are in these ranges, yes? What else
might be in those cells, and other adjacent cells?

Maybe just =COUNTIF(B1:AD10,"1"), you may not need the tricky list of
ranges you have.

 

[NoodNutt]

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:A4>0)/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:A4>0)/3, then = SUMIF(A1:A4>0)/2 or = SUMIF(A1:A4>0)/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.

 

[Pete_UK]

This will count the non-blank cells:

=COUNTIF(A1:Z1,"<>")

so you can use this as your divisor to get an average.

Alternatively, you can use this array* formula:

=AVERAGE(IF(A1:Z1<>0,A1:Z1))

* An array formula must be committed using the key combination of CTRL-
SHIFT-ENTER rather than the usual ENTER.

Hope this helps.

Pete

 

[Spiky]

Alternatively, you can use this array* formula:

=AVERAGE(IF(A1:Z1<>0,A1:Z1))

That is only necessary if the cells have zero in them, and zero is not
to be in the average calculation. If they are blank or have text, the
AVERAGE function simply does exactly what was requested. It averages
just the values, however many there are.

So all you would need is =AVERAGE(A1:A4)