Benches Uncategorized

3

Array formula in numbers

apple Verified

Imported from the Apple Support Forums via the Wayback Machine 🔗

Hi everyone,

I'm fairly new to Numbers and wanted to create an array formula in it. I read that this is not supported and one has to work around it.

I'm trying to achieve the below:

The formula is supposed to sum across column C if there is an x in column I and divide each amount in column C by the number of people, unless the person in row 1 matches the person in column E.

In the example (I14) the formula should sum I2:I6 and divide each entry by 8 (amount of people in column G). Since Anne did not pay anything (column E), nothing is subtracted from that amount.

Can anyone point me in the right direction? Thanks.

3 replies

Sign in or join Beachball Help to reply.

  1. 0

    Wayne Contello

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    You can try something like this:

    NOTE: Make the first row a header row and the last three rows footer rows.

    add a column after the count (which you can hide later):

    E2=B2/D2

    this is shorthand for... select cell E2 then type (or copy and paste from here) the formula:

    =B2÷D2

    to fill down, select cell E2, copy

    select cells E2 thru cell E7, paste

    Format the cells in the range F2 thru M7 as check boxes

    F10=SUMIFS($E, F, TRUE)

    F11=SUMIF($C, F$1, $B)

    F12=F$10−F$11

    to fill across, select the range F1 thru F12, copy

    select the range, F10 thru M12, paste

  2. 0

    jaxjason

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Just confirmation, Array formulas (also called CSE formulas in some circles of excel users) are not supported in Numbers. You will have to find other solutions (like Wayne suggested).

    jason

  3. 0

    Kojak1604

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Thank you very much Wayne. Very helpful :-).