Numbers. Sum cells with conditions

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.

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.

Hi Francisco,

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

Please help us to help you, by telling us what Column C contains.

I do not want to have to add additional columns.

You can hide Column D

Please tell us about the mysterious Column C. There are many helpful contributors here, but we need more information.

Regards,

Ian.

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.

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

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

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