numbers,sumif,non-contiguous,range

Question

numbers,sumif,non-contiguous,range

I am trying to sum positive non-zero non-contiguous ranges of cells on the web app for numbers on icloud.com. I have so far been unsuccessful in doing this because when I do it with a sum() function it will return the number of hours in decimals of an hour, instead of returning it as x hours y minutes. The web version doesn't have a way to format the cell as duration. even if i set it as duration it doesn't want to return as i want it displayed as x hours y mins. the only way it will do this is if the cells I'm summing are positive nonzero values. it seems to be a quirk of numbers because if I sum the same cells in Excel on windows it correctly returns x hours y minutes.

Is there any way to solve this? I'd greatly appreciate any help.

icloud-OTHER, Windows 7

Posted on Sep 12, 2017 9:18 PM

Reply

Answer 1

SGIII

Level 8

37,550 points

Sep 13, 2017 10:05 AM in response to desert_dweller5

If you enter the durations using the native notation like this, you can just sum the durations:

Numbers for iCloud doesn't seem to allow you to format the duration in : colon format (as you can do in Numbers for the Mac) but leaving the format as Automatic works, as long as you enter duration values with the h m s notation.

SG

Reply

Answer 2

Barry

Level 9

60,023 points

Sep 12, 2017 10:47 PM in response to desert_dweller5

Hi d-d'

You have two issues here: summing non-contiguous ranges, and displaying a duration sum in the manner you choose.

For the first, you may find some success using the UNIONRANGES function. IF that function is supported in Numbers for iCloud, the Function Help should provide you with syntax and some examples.

The second could be a simple matter of choosing the units to use. If you have specified only hours, then Numbers will either round the leftover minutes to the nearest hour, or display the leftover minutes as the (decimal) fraction of an hour.

If Numbers for iCloud lets you choose to display durations with both hours and minutes included, it should show an integer number of hours and an integer number of minutes, rounded to the nearest minute.

If the result you are seeing is a number (as opposed to a duration), it could be converted into a text value displaying hours and minutes using a formula like the one below. (The number is in cell A2, the formula in B2):

Disclaimer: Comments apply to Numbers for Mac, and may not hold for Numbers for iCloud, which has a smaller feature set.

Regards,

Barry

Reply