Array formula in numbers

Question

Level 1

8 points

Array formula in numbers

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.

MacBook Pro w/ Touch Bar (2018 or later)

Posted on Jan 10, 2019 7:37 AM

Reply

Answer 1

jaxjason

Level 5

5,073 points

Jan 10, 2019 11:20 AM in response to Kojak1604

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

Reply

Answer 2

Jan 10, 2019 11:00 AM in response to Kojak1604

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

Reply

Answer 3

Kojak1604 Author

Level 1

8 points

Jan 10, 2019 12:49 PM in response to Wayne Contello

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

Reply