Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

# Question:Q:Numbers. Sum cells with conditions

Hello

I have three columns full of numbers; A, B and C.

I need a cell to have a formula that gives me the result of sum the cells of column C when in their corresponding ones in the same row, the value of the cell of A is greater than the value of that of B. That is, if in A5 we have the value 5 and in B5 the value 4, the value of C5 must be sum to that of other values ​​of cells in C where the fact that in the same row the value of A is greater than that of B. I would also need to know the times that happens, that the value of A is greater than that of B in the same row.

Thanks

iPad Pro Wi-Fi, iOS 11, null

Posted on

Hi Francisco,

Try this. This table has a Header Row and two Footer Rows. Add another column (D)

Formula in D2 (and Fill Down)

=IF(A2>B2,"Yes","No")

Formula in Footer cell D7 =SUMIF(D,"=Yes",C)

Formula in Footer cell D8 =COUNTIF(D,"=Yes")

Regards,

Ian.

Nov 15, 2017 6:31 PM

Here's the same solution, expressed with slightly different formulas.

Presented as a curiosity—for readabiity, Ian's is better!

The three formulas shown are in:

D2, filled down to D6

D7

D8

Regards,

Barry

Nov 15, 2017 10:09 PM

Nov 16, 2017 12:31 AM in response to franciscofromalmeria In response to franciscofromalmeria

Thanks for your answers. My problem is something more complex and I do not want to have to add additional columns. I'm new to numbers and I'm looking for a solution similar to those found in openoffice, something like this:

sumproduct (a > b; c)

Is this possible in numbers?

thank you very much

Nov 16, 2017 12:31 AM

Hi Francis,

If this were possible in Numbers, do you not think one or the other of us would have mentioned it?

OpenOffice (and LibreOffice) were designed to behave very much like MS Excel.

Numbers is not (and was obviously never intended to be) an Excel clone.

MS chose to diversify some functions (such as SUMPRODUCT) to do things beyond what their name suggests. Apple, in the case of Numbers, appears to have chosen to stay within the bounds indicated by the name of the function:

The SUMPRODUCT function returns the sum of the products of corresponding numbers in one or more collections.

IF you want to add condition(s), then you need to use one of the functions with …IF or …IFS in its name. Ian (Yellowbox) chose SUMIF and COUNTIF to get the two results you are looking for.

The collection of test values must be the same size as the collection of sum values, which doesn't permit test values taking two columns and the sum values taking only one. Ian handles that issue by doing the comparison with a formula that produces a single value ("Yes" or "No") indicating whether the pair of values in each row of columns A and B meets the condition for being included or excluded from the SUM or COUNT, making it possible to place the (new) test values in a single column, fulfilling the requirement that the ranges holding the test values and the sum values be the same size.

The extra column is needed for the calculations, but it's not necessary that this column be visible.

You've said that your actual problem is "something more complex." Often the printer's statement that 'the devil is in the details' holds for spreadsheet issues as well. Would you care to share the details of this complexity?

Regards,

Barry

Nov 16, 2017 2:00 AM

Hi Francisco,

I have edited that table to help me understand the "more complex" problem.

You can hide Column D

Regards,

Ian.

Nov 16, 2017 3:05 AM

Nov 16, 2017 3:17 AM in response to Yellowbox In response to Yellowbox

Hi Francisco,

Another thought.

sumproduct (a > b; c)

Does your Region use comma (,) as the decimal separator?

For example, Pi

3,142

If so, then formulas use semicolon (;) to separate.

For example,

SUMIF(D;"=Yes";C)

Regards,

Ian.

Nov 16, 2017 3:17 AM

Nov 16, 2017 3:19 AM in response to franciscofromalmeria In response to franciscofromalmeria

The complexity is that I have many other columns with data and I would have liked to find a solution without having to add more columns, but if it is necessary to add more columns I will do it.

Nov 16, 2017 3:19 AM

Hi Francisco,

You can place the new column in another table. The formulas will continue to work.

You can move (Cut and Paste) Table 1-1 to "hide" it on another Sheet. The formulas will continue to work 😉.

Regards,

Ian.

Nov 16, 2017 3:28 AM

franciscofromalmeria wrote:

sumproduct (a > b; c)

Is this possible in numbers?

That's a really handy form of SUMPRODUCT(). It's not really an "extension" of what SUMPRODUCT() does. It's a so-called "array formula" version of SUMPRODUCT(). Excel and some other spreadsheet apps are able to manipulate a list or array of values in memory without taking up space in the cells grid on the spreadsheet. Numbers (with a few exceptions) doesn't support this, so you just to list the values or intermediate calculation in a range of cells. Hence the need for an extra column here.

SG

Nov 17, 2017 7:22 AM

User profile for user: franciscofromalmeria

Question: Numbers. Sum cells with conditions