SUM with COUNTIFS (multiple conditions)

Hi Marg0040,

here is an option.

This will count all entries that have Apples or Bananas, you must count them individually and then add them together. That must be done in two COUNTIFS.

If you use Apples and Bananas in one COUNTIFS it will only count the rows that met both conditions.

Formula for B2=COUNTIFS(Source::B,$A2,Source::C,"Apples")+COUNTIFS(Source::B,$A2,Source::C,"Bananas")

Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.

Hope this will solve your question, please let me know if this worked for you or if something in unclear.

Regards Ralf

Hi Marg,

SUM is not a good choice of function for this task. You are looking for a Count of the entries meeting two of three conditions.

The month number in column B of Table 2 must match the number in 'this row' of column A (in Table 1)

AND the text in column C of Table 2 must be "Apples" OR "Bananas".

Translating that into a formula for cell B2 of Table 1:

COUNTIFS(Table 2::B,A2,Table 2::C,"Apples")+COUNTIFS(Table 2::B,A2,Table 2::C,"Bananas")

Note that I did not include the absolute reference operator, $, in the references to cell A2.

The purpose of this operator is to keep the cell or range pinned to the same column ($A) as the formula is filled into cells to the left or right of its original location or pinned to the same row ($2) as the formula is filled up or down into rows above or below the original location.

In your case, you will likely be filling the formula down its column into cells B3, B4, etc., and recording the month number for each row in that row's cell in column A.

If you enter the A2 reference as $A$2, all copies of the formula in column B will continue to get the month number from cell A2. With the $ operators omitted, the formula will automatically increment to reference the next row as it is filled down the column, and will, as seen in the example above, return the correct count of the matching values in rows 7 and 9 of Table 2, etc.

Regards,

Barry

Marg0040 wrote:

count (sum) the number of entries in January that have Apples and/or Bananas.

If you have a recent version of Numbers then you can easily do this in seconds without writing a single formula.

Click in the data table (Table 2 in your example) and choose Organize > Create Pivot Table > On Current Sheet from the menu, then in the Pivot Options tab drag the Date and Fruit fields down into the boxes below as shown here:

With the Pivot Table still selected click the Filter tab and set the filter like this:

If you want a list of the items captured by your filter click in the relevant cell in the Pivot Table (in this example B2) and from the menu choose Organize > Create Table for Source Data. The result:

All of this took about a minute.

You can then quickly make a few cosmetic changes (rename tables if you want, etc) and you're done.

SG