# Difference between revisions of "Documentation/How Tos/Conditional Counting and Summation"

m |
(added interwiki link to fr article) |
||

Line 214: | Line 214: | ||

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. | 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. | ||

− | + | ||

+ | [[fr:Documentation/FR/Calc/Sommes et calculs conditionnels]] | ||

{{PDL1}} | {{PDL1}} |

## Revision as of 21:15, 17 February 2008

## Contents

- 1 Conditional Counting and Summation in Calc
- 1.1 COUNT
- 1.2 COUNTA
- 1.3 COUNTBLANK
- 1.4 SUM
- 1.5 SUBTOTAL
- 1.6 COUNTIF
- 1.7 SUMIF
- 1.8 DCOUNT, DCOUNTA, DSUM
- 1.9 Conditions in cell ranges
- 1.10 SUMPRODUCT
- 1.11 SUM with array formulas
- 1.12 DataPilot
- 1.13 Tips and Tricks: checking settings
- 1.14 Tips and Tricks: items between two dates
- 1.15 Tips and Tricks: summing the largest/smallest items
- 1.16 Tips and Tricks: summing matching blank, etc cells
- 1.17 Tips and Tricks: summing more than one column
- 1.18 Tips and Tricks: summing every nth row
- 1.19 Tips and Tricks: summing items with certain formatting
- 1.20 Tips and Tricks: summing matching items in a separate list

# 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`,

`or`

**COUNTA**`results for filtered data, that is data in cells chosen by a`

**SUM****filter**.

## COUNTIF

The COUNTIF function counts those items that meet a single condition. For example ` COUNTIF(A1:A4; “>4”)` counts the cells in

`that are greater than`

**A1:A4**`.`

**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

`that correspond to “`

**B1:B4**`” entries in`

**red**`.`

**A1:A4**## DCOUNT, DCOUNTA, DSUM

The DCOUNT function,
DCOUNTA function, and
DSUM function perform similarly to ` COUNT`,

`and`

**COUNTA**`, except that the cells to be counted or summed are chosen according to a table of conditions. For example,`

**SUM**`counts the number of rows of`

**DCOUNT(A1:C5; 0; E6:F7)**`for which the multiple conditions specified in`

**A1:C5**`are all true.`

**E6:F7**## 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

`a list of sizes, then we can enter in cell`

**B1:B6**`the formula`

**D1**`, which returns`

**=(A1=”red”)**`or`

**TRUE**`depending if cell`

**FALSE**`is`

**A1**`or not. Alternatively, we can enter in cell`

**red**`the formula`

**D1**`, which returns`

**=AND(A1=”red”; B1=”big”)**`if cell`

**TRUE**`is`

**A1**`AND cell`

**red**`is`

**B1**`and`

**big**`otherwise. Copy and paste this formula to`

**FALSE**`and we have a range of cells which are`

**D2:D6**`if the conditions are met and`

**TRUE**`otherwise.`

**FALSE**In numerical calculations, ` TRUE` is treated as

`, and`

**1**`is treated as`

**FALSE**`. So entering`

**0**`will simply sum those`

**=SUM(D1:D6)**`s and`

**1**`s, and give us the count of items that are both`

**0**`AND`

**red**`.`

**big**

In fact, because ` TRUE` and

`evaluate as`

**FALSE**`and`

**1**`, we do not need the`

**0**`function - in`

**AND**`we can simply write`

**D1**`, and copy/paste down to`

**=(A1=”red”)*(B1=”big”)**`.`

**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**`items. In`

**red**`we write`

**D1**`, and copy/paste down to`

**=(A1=”red”)*(B1=”big”)*C1**`.`

**D2:D6**`will contain the weight in`

**D1**`if the conditions are met (and zero otherwise) and so on for`

**C1**`, Therefore`

**D2:D6**`will now give us the total weight.`

**=SUM(D1:D6)**Alternatively, it is possible to fill ` D1:D6` with an array formula. In

`, write`

**D1**`, and enter by pressing Ctrl_Shift_Enter. All the cells in`

**=(A1:A6=”red”)*(B1:B6=”big”)*C1:C6**`now show the desired weights as before.`

**D1:D6**## 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”`,

`and`

**B1:B6=”big”**`may be treated as 3 separate arrays, not displayed but internally calculated.`

**C1:C6**` =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 + ...

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

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.

## 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.

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")

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

`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.`

**DATEVALUE**## 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.

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)))

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

`simply adds them up. We could use`

**SUMPRODUCT**`instead of`

**SUM**`but in that case the formula must be entered as an array formula by pressing Ctrl_Shift_Enter.`

**SUMPRODUCT**

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`,

`) can be used in the same way.`

**ISNUMBER**## Tips and Tricks: summing more than one column

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

will sum cells in ` E1:E6` and in

`which correspond to cells in`

**F1:F6**`containing`

**D1:D6**`. For example if`

**red**`and`

**D2**`contain red, the result is`

**D4**`.`

**E2+F2+E4+F4**## 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

`to`

**=0**`to sum every second row, but starting at the first row. Change the`

**=1**`to`

**2**`to sum every third row, and so on.`

**3**## 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,

`contains phone numbers and`

**B1:B9**`the costs of making phone calls.`

**C1:C9**`is a list of certain phone numbers, and you want to know the total cost of calls to these numbers.`

**F1:F5**

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.

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). |