Multiconditional aggregation of data

Aggregation of data (summarization, counting, averaging) can be done with the help of functions that have one or more of the conditions under which calculation is performed. These are very useful functions because they allow complex analyzes and an efficient way to make summary reports. In the following text we will describe these functions through several examples …

First, let’s say something about multiconditional summarization. For this purpose we’ll use SUMIFS function which has following syntax:

SUMIFS (<sum range>,<criteria range₁>, <criterion₁>,…,<criteria range_n>, <criterion_n>)

Prvi argument kriterijum je opseg za sumiranje. Opseg je skup ćelija u nekoj koloni tabele, cela kolona ili imenovani niz koji predstavlja kolonu. U ovom slučaju, radi se o opsegu koji sadrži vrednosti koje želimo da sumiramo. Zatim, dodaju se parovi opsega vrednosti i kriterijuma. Minimalno se mora navesti bar jedan par, a maksimalan broj parova je 127 za svaku od funkcija za višeuslovno združivanje, što ih i čini veoma moćnim alatom.

The first argument is a sum range, which is a range of cells within a column, entire column or a named range. Further we add pairs that represent criterions and their ranges. Minimum is one pair, and maximum consists of 127 pairs of criteria, which makes this very powerful function.

Let’s show their use in the example. Let’s say we want to aggregate the sales value of fruit juices (VOĆNI SOKOVI) that were made in the city of Belgrade (BEOGRAD). We sum up the formula:

=SUMIFS(C2:C7,B2:B7,”BEOGRAD”,A2:A7,”VOĆNI SOKOVI”)

The first argument of the function is the range C2: C7, which represents the values we are summarizing. Then we quote the B2: B7 rangem, which contains cells with town names, and then we quote the value “BEOGRAD”. Another condition is determined by specifying the range of cells where the product types  are present (A2: A7), and as a condition we indicate the type of product “VOĆNI SOKOVI”. Entering the functions will result in the summarization of cells that meet both required conditions.

If we wanted to calculate average sales for the given conditions, we would use the function AVERAGEIFS, which has the same syntax as the SUMIFS function. On that occasion, we would enter the following formula:

=AVERAGEIFS(C2:C7,B2:B7,”BEOGRAD”,A2:A7,”VOĆNI SOKOVI”)

At last, here is a function for multiconditional counting:

COUNTIFS (<criteria range₁>, <criterion₁>,…,<criteria range_n>, <criterion_n>)

For example, if we want to count the number of cells that contain the name of the town “BEOGRAD”, we use the following formula for the type of product “FOOD SOURCES”:

=COUNTIFS(B2:B7,”BEOGRAD”,A2:A7,”VOĆNI SOKOVI”)

Kao i kod jednouslovnih funkcija za združivanje podataka, kada je uslov neka tekstualna vrednost možemo koristiti “džoker znakove”, odnosno “zvezdicu” (*) koja menja više znakova ili “upitnik” (?) koji menja jedan znak u navedenom tekstu. Ukoliko je uslov brojna vrednost, možemo da iskoristimo standardne operatore za poređenje koje navodimo pod znakovima navoda. Na primer: “>100000”, “<=500” itd. U slučaju da testiramo uslov na jednakost dovoljno je da kao argument unesemo brojnu vrednost sa kojom se porede članovi zadatog opsega podataka.

As with single-condition data aggregation functions, whenever a textual value is allowed, we can use “joker characters” or asterisk (*) that changes more characters or question mark (?) that changes only one character in the text. If the condition is of a numeric value, we can use the standard comparison operators that we make under the indictment. For example: “> 100000”, “<= 500” etc. In case we test the condition for equality, it is enough to include as an argument the numerical value which will be used for comparation.