Struggling with this correct formula in Numbers

Question

Level 1

4 points

Struggling with this correct formula in Numbers

HI there everyone,

I’ve got two tables side by side, lets call the table on the left Table A and on the right Table B.

and what I’m trying to calculate is this the “Total” in Table B.

there are 5 columns named, “Share”, “Quad”, “Triple”, “Double” and “Room” in Table A, which need to be multiplied by “Share days”, “Quad days”, “Triple days”, “Double days” and “Room days” in Table B.

SUMPRODUCT((((Activities::C3×B3) + (Activities::D3×C3) + (Activities::E3×D3) + (Activities::F3×E3)+ + (Activities::G3×F3) + IF(ISNUMBER(H3), H3 × G3, 0) + IF(ISNUMBER(I3),I3 ×J3,0))))

This is the formula I’m using but it only works for single cells in both tables. I tried dragging these single cells to the whole column down, but If I enter any numeric value in Table B, under the “days” column, it doesn’t work.

SUMPRODUCT((((Activities::C3:C18×B3:B18) + (Activities::D3:D18×C3:C18) + (Activities::E3:E18×D3:D18) + (Activities::F3:F18×E3:E18)+ + (Activities::G3:G18×F3:F18) + IF(ISNUMBER(H3), H3 × G3, 0) + IF(ISNUMBER(I3),I3 ×J3,0))))

Even after selecting the whole column, I’m still not able to change the “Total” column except for the first row.

Can anybody please tell me what I’m doing wrong, FYI I’m new to Apple Numbers.

Posted on Aug 31, 2024 2:07 AM

Reply

Answer 1

SGIII

Level 8

36,134 points

Aug 31, 2024 6:00 AM in response to eihabs01

When struggling with long formulas, it sometimes helps to go back to basics and try to use simpler formulas and document design (avoid merged cells!).

Try something like this to see how SUMPRODUCT works.

As long as the two ranges have the same dimensions then SUMPRODUCT can do all the work for you in one short formula.

=SUMPRODUCT(Table A::A2:D3,Table B::A2:D3)

The 'Multiplying it out' table does the same thing with more formulas, each very simple. In A2 it's just

=Table A::A2*Table B::A2

More on SUMPRODUCT here:

SUMPRODUCT - Apple Support

SG

Reply

Answer 2

SGIII

Level 8

36,134 points

Sep 1, 2024 5:48 AM in response to eihabs01

I suggest getting rid of the merged cells at the top and making sure the shaded rows are Header Rows. Make the last row is a Footer Row.

The formula to multiply the rate by the days and total up the results would be something like this for all the rows:

=SUMPRODUCT(Table A::C3:G18,Table B::B3:F18)

If you are calculating row by row (not all the rows) then for Row 3 you would have something like this:

=SUMPRODUCT(Table A::C3:G3,Table B::B3:F3)

... and fill that down into the rows below.

You of course need to substitute in the real names of your tables where you see Table A and Table B in the formula.

For further calculations using the values in G:K make sure those values are in single cells rather than merged cells, perhaps in a separate table.

SG

Reply

Answer 3

eihabs01 Author

Level 1

4 points

Sep 1, 2024 11:14 AM in response to SGIII

ALAS! It's working!

"When struggling with long formulas, it sometimes helps to go back to basics and try to use simpler formulas and document design"

This line absolutely nailed it in the head for me, I deleted the whole formula I wrote it again step by step and cell by cell just so the sheet understands what exactly that I'm trying to do.

=SUMPRODUCT(Activities::C3:G18,B3:F18)+H3×G3+I3×J3

This is what I used. Thank you so much for all the help!

Reply

Answer 4

eihabs01 Author

Level 1

4 points

Sep 1, 2024 3:14 AM in response to Camelot

Reply

Answer 5

Camelot

Level 9

55,329 points

Aug 31, 2024 4:23 PM in response to eihabs01

It would help if you showed more of the sheet. As it is, we can't see the column numbers, so it isn't completely clear which columns you're trying to multiply out)

Reply