Add cells if corresponding cell contains a value in Numbers

Question

Level 1

4 points

Add cells if corresponding cell contains a value in Numbers

I would like Numbers to scan a column, and if a cell in column A contains a number, it would sum the corresponding values in column B. For a simple example, in the pictured table, the values in B5, B7, and B10 would be excluded from the sum because the corresponding cells in column A do not contain numbers.

When I try SUMIF(A3:A10,ISNUMBER(A3:A10),SUM(B3:B10)) I receive an error stating, "The range A3:A10 can't be used as a single value."

I have tried playing around with the formula above, but I never seem to get the expected result.

Posted on Jul 22, 2024 6:19 AM

Reply

Answer 1

Top-ranking reply

HRA87 Author

Level 1

4 points

Jul 22, 2024 3:13 PM in response to Badunit

Thank you very much for your help. I had been working in Excel but am switching to Numbers and didn't realize I could not work with "array" formulas in the same way.

You did help me create a workaround. I used the IF(ISNUMBER(A),B,0) for each row and then found the sum. I did not want the extra collum, so I used one cell to sum everything up one row at a time. Since this is for a sheet that will be duplicated and then manipulated monthly, it seemed worth the time to create this long formula as I would only have to do so once.

l am learning to code so I used this to practice and wrote a program that created the formula for me using a while loop to add each row. It was fun practice. I tried it for my little sample table, and it spat out this, which worked perfectly when I pasted it into Numbers:

SUM(IF(ISNUMBER(A2),B2,0),IF(ISNUMBER(A3),B3,0),IF(ISNUMBER(A4),B4,0),IF(ISNUMBER(A5),B5,0),IF(ISNUMBER(A6),B6,0),IF(ISNUMBER(A7),B7,0),IF(ISNUMBER(A8),B8,0),IF(ISNUMBER(A9),B9,0),IF(ISNUMBER(A10),B10,0))

As for the header text, I was working quickly to create the sample and didn't realize I had not actually placed it in the header row. I'll keep an eye on that in the future 😂

Reply

Answer 2

Badunit

Level 7

31,341 points

Jul 22, 2024 7:44 AM in response to HRA87

ISNUMBER accepts a single cell as input, not a range. Its result is a single value, not an array of values. Excel has "array" formulas that will let you do what you are trying to do in one formula but Numbers does not.

If the non-number rows are blank like in your screenshot, you can use SUMIF to sum only those rows that are not blank.

=SUMIF(A,"<>",B)

If those rows might have text in them, I think you will need a column of formulas to do ISNUMBER for each row.

Formula in column C could be = IF(ISNUMBER(A),B,0)

Then use SUM(C) to sum them up

You might also want to put your header text (Reference and Values) into the header row (row 1) instead of in the data rows.

Reply

Answer 3

Badunit

Level 7

31,341 points

Jul 23, 2024 6:15 AM in response to HRA87

Another alternative is to use a text-based approach. Two possibilities are

=SUMIF(A,REGEX("^\d+$"),B)

or

=SUMIF(A,REGEX("^[0-9.,]+$"),B)

The first one requires your numbers in A to be integers with no commas and no decimal separator/point. It is looking for a string that is all digits. The second allows points and commas but it will think a lone comma or point or any combination of commas and points is a number. So a string like ",,.,,." would be a "number" as far as it is concerned.

Reply

Add cells if corresponding cell contains a value in Numbers

Similar questions