Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Problem requiring help
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Problem requiring help
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. -- Bucky F "NoodNutt" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Problem requiring help
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-- -- RyGuy "willfeld" wrote: 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. -- Bucky F "NoodNutt" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Problem requiring help
On Apr 29, 9:27 pm, "NoodNutt" wrote:
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Problem requiring help
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Problem requiring help
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 On May 1, 1:56*am, "NoodNutt" wrote: 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Problem requiring help
On Apr 30, 8:09 pm, Pete_UK wrote:
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Requiring data in certain cells | Excel Worksheet Functions | |||
Excel start-up requiring Frontpage disk? | Excel Discussion (Misc queries) | |||
requiring a field | Excel Worksheet Functions | |||
Formulas requiring a time stamp | Excel Worksheet Functions | |||
Formula requiring two different criterias | Excel Worksheet Functions |