Documentation/How Tos/Conditional Counting and Summation

Conditional Counting and Summation in Calc

This is a review of various ways to count and sum the contents of cells conditionally, depending on the result of some test.

COUNT

The COUNT function counts the number of cells which contain numbers and will ignore any others. For example text cells are ignored.

COUNTA

The COUNTA function counts the number of cells which contain anything (text, numbers, errors, logical values, formulas). It ignores empty (blank) cells.

COUNTBLANK

The COUNTBLANK function counts the number of empty (blank) cells.

SUM

The SUM function sums all the numbers in the specified cells. See later for how to use it as a conditional function.

SUBTOTAL

The SUBTOTAL function returns COUNT, COUNTA or SUM results for filtered data, that is data in cells chosen by a filter.

COUNTIF

The COUNTIF function counts those items that meet a single condition. For example COUNTIF(A1:A4; ">4") counts the cells in A1:A4 that are greater than 4.

SUMIF

The SUMIF function sums those items that meet a single condition. For example SUMIF(A1:A4; "=red"; B1:B4) sums the values in B1:B4 that correspond to “red” entries in A1:A4.

DCOUNT, DCOUNTA, DSUM

The DCOUNT function, DCOUNTA function, and DSUM function perform similarly to COUNT, COUNTA and SUM, except that the cells to be counted or summed are chosen according to a table of conditions. For example, DCOUNT(A1:C5; 0; E6:F7) counts the number of rows of A1:C5 for which the multiple conditions specified in E6:F7 are all true.

Conditions in Cell Ranges

One simple method to count or sum using multiple conditions is to enter those conditions in a new row or column. For example, if A1:A6 contains a list of colours and B1:B6 a list of sizes, then we can enter in cell D1 the formula =(A1="red"), which returns TRUE or FALSE depending if cell A1 is red or not. Alternatively, we can enter in cell D1 the formula =AND(A1="red"; B1="big"), which returns TRUE if cell A1 is red AND cell B1 is big and FALSE otherwise. Copy and paste this formula to D2:D6 and we have a range of cells which are TRUE if the conditions are met and FALSE otherwise.

example

In numerical calculations, TRUE is treated as 1, and FALSE is treated as 0. So entering =SUM(D1:D6) will simply sum those 1s and 0s, and give us the count of items that are both red AND big.

In fact, because TRUE and FALSE evaluate as 1 and 0, we do not need the AND function - in D1 we can simply write =(A1="red")*(B1="big"), and copy/paste down to D2:D6.

Now let us say that C1:C6 contains a list of weights of these items, and we wish to know the total weight for all big red items. In D1 we write =(A1="red")*(B1="big")*C1, and copy/paste down to D2:D6. D1 will contain the weight in C1 if the conditions are met (and zero otherwise) and so on for D2:D6, Therefore =SUM(D1:D6) will now give us the total weight.

example

Alternatively, it is possible to fill D1:D6 with an array formula. In D1, write =(A1:A6="red")*(B1:B6="big")*C1:C6, and enter by pressing Ctrl_Shift_Enter. All the cells in D1:D6 now show the desired weights as before.

SUMPRODUCT

The SUMPRODUCT function can be used to perform the counting and summation calculations in the previous section, without using extra columns. It is necessary to understand array formulas to understand this.

Using the summation example from the previous section, A1:A6="red", B1:B6="big" and C1:C6 may be treated as 3 separate arrays, not displayed but internally calculated.

=SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6) will multiply corresponding elements of the arrays together and return their sum, i.e.:

(A1="red")*(B1="big")*C1 + (A2="red")*(B2="big")*C2 + ...

example

This again gives us the total weight, without requiring an extra column.

For portability to Excel, use =SUMPRODUCT((A1:A6="red") * (B1:B6="big") * C1:C6), as Calc and Excel treat logical results differently.

Notice that SUMPRODUCT formulas are simply entered by pressing the Enter key - they do not require Ctrl_Shift_Enter even though arrays are involved.

Also, be aware that calculations using very large arrays take a lot of computer processing time, and may slow the spreadsheet down.

SUM with Array Formulas

An alternative to SUMPRODUCT is to use the SUM function. The previous example would be written:

=SUM( (A1:A6="red")*(B1:B6="big")*C1:C6) )

and entered as an array formula by pressing Ctrl_Shift_Enter. As with SUMPRODUCT, this works by multiplying corresponding elements of the arrays together and returning their sum.

See Using Arrays for details of array formulas and more examples.

DataPilot

Another way to approach conditional counting and summation is to use the DataPilot to generate an interactive table, whereby data can be arranged and summarised according to different points of view.

Tips and Tricks: Checking Settings

When matching text with some functions (such as SUMIF) , the results may well depend on the settings on the Tools menu-> Options-> OpenOffice.org Calc-> Calculate page. If the user's settings are incorrect, the results may therefore be wrong.

settings

One solution is to include prominently in the spreadsheet a check that the settings are correct. For example:

=IF(ISERR(SEARCH(".";"a"));"ERROR: please enable regular expressions";"")

will show an error message if regular expressions are disabled.

Another example - in cell A3 enter the text:

Check: 

In cell A4 enter:

="Regular expressions are "&IF(COUNTIF(A3;".*"); "enabled"; "disabled")

In cell A5 enter:

="Whole cell matching is "&IF(COUNTIF(A3;"<>e"); "enabled"; "disabled")

checking the settings

or better, use appropriate error messages.

Tips and Tricks: Items Between Two Dates

Dates are stored internally as numbers and can thus be compared easily. For example, to count the number of cells in A1:A6 between two dates, you could use:

=SUMPRODUCT(A1:A6>DATEVALUE("5 Nov 06"); A1:A6<DATEVALUE("5 Dec 06"))

If you express the dates with slashes (e.g. “1/2/2005”) you can dispense with the DATEVALUE function, as Calc will convert the date. However, be aware that in one country this text may be converted to 1Feb05 and in another to 2Jan05.

Tips and Tricks: Summing the Largest/Smallest Items

To add up the largest 3 numbers in A1:A5, the most straightforward method is to enter:

=LARGE(A1:A5; 1) in cell B1

=LARGE(A1:A5; 2) in cell B2

=LARGE(A1:A5; 3) in cell B3

so that the largest 3 numbers are in B1:B3, and then use the formula:

=SUM(B1:B3) to give the result.

example

This method is very clear, and generally therefore to be recommended.

However if you wish to derive the same result in a single cell, you could use

=SUMPRODUCT(LARGE(A1:A5; ROW(A1:A3)))

example

Here ROW(A1:A3) is a 1 column 3 row array containing the numbers 1, 2, 3.

LARGE(A1:A5; ROW(A1:A3)) is then a 1 column 3 row array containing the largest 3 numbers and SUMPRODUCT simply adds them up. We could use SUM instead of SUMPRODUCT but in that case the formula must be entered as an array formula by pressing Ctrl_Shift_Enter.

To add up the largest 4 numbers (say), use ...ROW(A1:A4).. instead of ...ROW(A1:A3)..

To add the smallest numbers, use SMALL(...) instead of LARGE(...).

Tips and Tricks: Summing Matching Blank, etc. Cells

=SUMPRODUCT(ISBLANK(A1:A5); B1:B5)

sums those cells in B1:B5 corresponding to blank cells in A1:A5.

Other similar functions (e.g. ISTEXT, ISNUMBER) can be used in the same way.

Tips and Tricks: Summing More Than One Column

=SUMPRODUCT(D1:D6="red";E1:E6+F1:F6)

will sum cells in E1:E6 and in F1:F6 which correspond to cells in D1:D6 containing red. For example if D2 and D4 contain red, the result is E2+F2+E4+F4.

Tips and Tricks: Counting How Many Odd Numbers

=SUMPRODUCT(MOD(A1:A8; 2))

will return the number of numbers in A1:A8 which are odd (1, 3, 5, 7,...), and

=SUMPRODUCT(MOD(A1:A8; 2) = 0)

will return the number which are even.

Tips and Tricks: Summing Every nth Row

=SUMPRODUCT(MOD(ROW(A1:A8); 2)=0;  A1:A8)

will sum every second row in A1:A8. Change the =0 to =1 to sum every second row, but starting at the first row. Change the 2 to 3 to sum every third row, and so on.

example

Tips and Tricks: Summing Items With Certain Formatting

The CELL function returns information about cells, for instance the format that numbers or dates are displayed in and the column width. It can thus be used with one of the methods above. There is no function that returns the colour or font of a cell. If no independent formula relating to colour or font exists, then it may be necessary to use a macro.

Tips and Tricks: Summing Matching Items in a Separate List

Say that A1:A9 contains a list of dates, B1:B9 contains phone numbers and C1:C9 the costs of making phone calls. F1:F5 is a list of certain phone numbers, and you want to know the total cost of calls to these numbers.

In cell D1 enter:

=ISNUMBER(MATCH(B1; F$1:F$5; 0))*C1

and copy/paste down to D2:D9

=SUM(D1:D9) now gives the total cost of calls.

example

To perform this calculation without an extra column, you could use:

=SUMPRODUCT(NOT(ISERROR(MATCH(B1:B9; F$1:F$5; 0))); C1:C9)

We discussed above how it can be important to check Calc settings; this is a good example - if the user has regular expressions turned on (the default) a telephone number written as (720) 528-1700 is interpreted as a regular expression and might match entries other than (720) 528-1700.

Content on this page is licensed under the Public Documentation License (PDL).