Documentation/Calc Functions/SUM.HVIS

From The Document Foundation Wiki
Jump to navigation Jump to search
This page is a translated version of the page Documentation/Calc Functions/SUMIF and the translation is 3% complete.
Outdated translations are marked like this.
Other languages:
English • ‎Nederlands • ‎dansk • ‎español • ‎čeština

Template:Documentation/Calc Functions/Func Page Links/da

Funktionsnavn:

SUM.HVIS

Kategori:

Matematisk

Resume:

Adderer de celler, der er bestemt med et givet kriterium. Denne funktion bruges til at bladre gennem et område, når du søger efter en bestemt værdi.

The criterion passed to SUM.HVIS can utilize wildcards or regular expressions.

Syntaks:

SUM.HVIS(Range; Criterion[; SumRange])

Returnerer:

Returns a real number that is the result of adding together the numbers in relevant cells.

Argumenter:

Range Argument

Range specifies the cells to be matched and, when the SumRange argument is omitted, the cells to be summed. Range usually takes one of the following forms:

  • A reference to a cell range (for example, A1:A25). If SumRange is omitted, then Range may utilize the reference concatenation operator (~).
  • The name of a named range.
  • The name of a database range.

Criterion argument

Criterion is the criterion for matching, or a cell containing that criterion. Criterion can take one of the following forms:

  • A number, such as 34.5. Dates and logical values (SAND or FALSK) are treated as numbers.
  • An expression, such as 2/3, KVROD($D$1), or DATO(2021; 11; 1).
  • A text string, such as "golf" or "<>10".

SUM.HVIS looks for cells in Range that are equal to Criterion, unless Criterion is a text string that starts with a comparator (>, <, >=, <=, =, or <>). In the latter case SUM.HVIS compares the cells in Range with the remainder of the text string (interpreted as a number if possible and text otherwise). For example, the condition ">4.5" tests if the content of each cell is greater than the number 4.5, the condition "<dog" tests if the content of each cell comes alphabetically before the text "dog", and the condition "<>2021-11-01" tests if the content of each cell is not equal to the specified date.

Criterion supports the following specific behaviors:

  • The string "=" matches empty cells. For example the formula =SUM.HVIS(B1:B10; "="; A1:A10) returns the sum of all values in the range A1:A10 if all cells in the range B1:B10 are empty. Note that "=0" does not match empty cells.
  • The string "<>" matches non-empty cells. For example the formula =SUM.HVIS(C1:C10; "<>"; A1:A10) returns the sum of all values in the range A1:A10 if there are no empty cells in the range C1:C10.
  • If the value after the <> comparator is not empty, then Criterion matches any cell content except that value, including empty cells.

SumRange argument

SumRange specifies the cells to be summed and takes one of the forms listed for Range. SumRange should normally have the same dimensions as Range – if this is not the case, SUM.HVIS will sum a range of cells starting at the first cell in SumRange and having the same dimensions as Range.

Error conditions

  • If the SumRange argument is provided and the reference concatenation operator is utilized within Range, then SUM.HVIS reports a parameter list error (Err:504).

Yderligere oplysninger:

Søgningen understøtter jokertegn eller regulære udtryk. Når regulære udtryk er aktiveret, kan du for eksempel indtaste "alle.*", for at finde den første forekomst af "alle" fulgt af vilkårlige tegn. Hvis du vil søge efter en tekst, der også er et regulært udtryk, skal du enten sætte tegnet "\" foran hvert tegn eller indramme teksten med \Q...\E. Du kan slå den automatiske evaluering af jokertegn eller regulære udtryk til og fra under Funktioner > Indstillinger > LibreOffice Calc > Beregn.

  • SUM.HVIS allows only one matching criterion. If you need to sum cells based on multiple criteria, use the SUM.FLERE.HVIS function.
  • The default matching performed by SUM.HVIS is case-insensitive. However, a case-sensitive match can be carried out when using a regular expression by including a mode modifier "(?-i)" within the regular expression, as demonstrated by one of the examples below.
  • The behavior of SUM.HVIS is affected by several settings available on the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate dialog (LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS).
  1. If the checkbox is ticked for Search criteria = and <> must apply to whole cells, then the condition "red" will match only "red"; if unticked it will match "red", "Fred", "red herring".
  2. If the checkbox is ticked for Enable wildcards in formulas, the condition will match using wildcards - so for example "b?g" will match "bag", "beg", "big", "bog", and "bug".
  3. If the checkbox is ticked for Enable regular expressions in formulas, the condition will match using regular expressions - so for example "r.d" will match "red", "rid", and "rod", while "red.*" will match "red", "redraw", and "redden".
  4. The setting of the Case sensitive checkbox has no impact on the operation of SUMIF.

Template:Documentation/Calc Functions/regular expressions information/da

Template:Documentation/Calc Functions/wildcards information/da

Eksempler:

Basic examples

Template:Calc-Formula/da Template:Calc-Description/da Template:Calc-Returns/da
=SUM.HVIS(A1:A9; "<0") where cells A1 to A9 contain the numbers -1, 2, -3, 4, -5, 6, -7, 8, and -9 respectively. Here the function sums the negative numbers in the cell range A1:A9. -25
=SUM.HVIS(A1:A9; F1) where cells A1 to A9 contain the numbers -1, 2, -3, 4, -5, 6, -7, 8, and -9 respectively and cell F1 contains the string ">=0" (entered without typing the double quotes). Here the function sums the non-negative numbers in the cell range A1:A9. 20
=SUM.HVIS(B2:B4; "<"&F2; C2:C4) where cells B2 to B4 contain the numbers 7, 9, and 11 respectively; cells C2 to C4 contain the numbers 4, 5, and 6 respectively; and cell F2 contains the number 10. Here the function determines that the values in cells B2 and B3 are less than 10 and so sums the values in cells C2 and C3. 9
=SUM.HVIS(D1:D9; "apples"; E1:E9) where cells D1, D4, D5, D6, and D9 contain the string "apples"; cells D2, D3, D7, and D8 contain the text "pears"; and cells E1 to E9 contain the numbers 12, 19, 10, 27, 97, 73, 79, 71, and 40 respectively, representing a quantity of the corresponding fruit. Here the function calculates the total quantity of apples. 249

Other examples

The examples in this subsection are based on a small database of sales data for sports equipment, with the data organized as in the following table.

A B C D E
1 Date Sales Value Category Region Employee
2 2021-10-02 $1,508 Golf East Hans
3 2021-10-02 $410 Tennis North Kurt
4 2021-10-02 $2,340 Sailing South Ute
5 2021-10-03 $4,872 Tennis East Brigitte
6 2021-10-06 $3,821 Tennis South Fritz
7 2021-10-06 $2,623 Tennis East Fritz
8 2021-10-07 $3,739 Golf South Fritz
9 2021-10-08 $4,195 Golf West Ute
10 2021-10-10 $2,023 Golf East Hans
Template:Calc-Formula/da Template:Calc-Description/da Template:Calc-Returns/da
=SUM.HVIS(B2:B10; ">=4000") Here the function sums the numeric entries in the Sales Value data that are greater than or equal to $4,000. $9,067
=SUM.HVIS(E2:E10; "ute"; B2:B10) Here the function locates the entries for Ute in the Employee data and sums the corresponding numeric entries in the Sales Value data. $6,535
=SUM.HVIS(CategoryData; "golf"; B2:B10) where the named range CategoryData has been created to cover the cell range C2:C10. Here the function locates the entries for Golf in the Category data and sums the corresponding numeric entries in the Sales Value data. $11,465
=SUM.HVIS(D2:D10; F1; B2:B10) where cell F1 contains the text ">=south" (entered without typing the double quotes). Here the function locates the entries for South and West in the Region data and sums the corresponding numeric entries in the Sales Value data. $14,095
=SUM.HVIS(A2:A10; DATO(2021; 10; 2); B2:B10) Here the function locates the entries for 2021-10-02 in the Date column and sums the corresponding numeric entries in the Sales Value data. $4,258
=SUM.HVIS(A2:A10; ">="&DATO(2021; 10; 7); B2:B10) Here the function locates the entries dated on or after 2021-10-07 in the Date column and sums the corresponding numeric entries in the Sales Value data. $9,957
=SUM.HVIS(D2:D10; "????"; B2:B10) This example will only work as described here if wildcards are enabled. Here the function locates the four-character entries in the Region data (East and West) and sums the corresponding numeric entries in the Sales Value data. $15,221
=SUM.HVIS(E2:E10; "^f.*"; B2:B10) This example will only work as described here if regular expressions are enabled. Here the function locates the entries in the Employee data that start with the letter "f" or "F" (Fritz) and sums the corresponding numeric entries in the Sales Value data. $10,183
=SUM.HVIS(E2:E10; "(?-i)ute"; B2:B10) This example will only work as described here if regular expressions are enabled. The "(?-i)" mode modifier within the regular expression changes to a case-sensitive match and so no entries are found in the Employee data. Contrast this with the second example in this table. $0

Beslægtede LibreOffice-funktioner:

TÆL.HVIS

SUM

SUM.FLERE.HVIS

ODF standard:

Section 6.16.62, part 2

Tilsvarende Excel-funktioner:

SUM.HVIS