Contents
For selective counting by several conditions in large tables, you can use several methods: filters, pivot tables, functions SUMMESLI и SUMMESLIMN etc.
Another, relatively exotic, but very powerful tool is the function BSSUMM (DSUM) from category Working with the database (Database). With external simplicity, it allows you to flexibly filter lists by several complex and interconnected conditions and calculates the sum of the found records for a given column. The function syntax is:
=BSMD(Initial data; result_column; Condition_range)
where
- Initial data – a range that includes a table with the data that we are analyzing and a header row.
- result_column – the name (from the table header) or the serial number of the column by which you want to sum the data.
- Condition_range – a range containing the names of the columns and conditions on them.
Let’s look at the details and nuances of using this wonderful feature in practice. Let’s say we have the following table with sales data:
To make it more convenient to refer to this table in the future, we convert it to a “smart” one with the command Format as a table tab Home (Home — Format as Table) or keyboard shortcut Ctrl+T. On the tab that then appears Constructor (Design) give it a name – for example BaseData.
Simple sum by one condition
Let’s start with a simple case. Let’s say we need to sum the cost of all juice deals kind. It will look like this:
Pay attention to the following points:
- It’s not enough just to write the name of a smart table Database in the first argument, because in this case, the link does not include a header, but the BDSUMM function requires it. Therefore, the tag is added to the name [#All] or, in the English version [#All].
- The column by which you want to sum the data can be specified either by the name (“Cost”) or by the number (it would be 5).
- The name of the column in the yellow range of conditions must match the name in the source table one-to-one.
- The BDSUMM function is case-insensitive (kind = GOOD = GOOD = GOOD, etc.)
- So that the criterion in the yellow cell A2 Excel does not begin to be understood as a formula (because formulas usually begin with an equal sign), you can use text format or simply start entering the cell with an apostrophe:
Approximate and exact text search
If necessary, you can easily implement not only exact, but also approximate text search. Look carefully at the following options for summing up revenue by city Abakan and the difference between them:
- If you need to search for an exact match, then use the construction ‘= (apostrophe and equal sign).
- If you need a substring search, i.e. all cells that contain the desired value, then it must be enclosed in asterisks. In our case, all variants of Abakan will be summed up (with “g”, without “g”, with spaces before and after, etc.)
- If you just enter a value without equals and asterisks, then all lines where the content begins with the specified value will be found and summed up, i.e. this is equivalent to an asterisk at the end.
Several conditions with links “AND” – “OR”
If you need to sum data for several conditions connected to each other by the logical AND operator (AND), then the cells with these conditions should be in one line. For example, if you want to sum up all sales Fanta by Abakan (in any form of writing it), then it will look like this:
If you need to link several conditions with the logical operator OR (OR), then they need to be placed in different lines. For example, if you need to sum up the money for all spellings of “city on the Neva”, of which there are a great many:
And of course, you can combine both approaches, combining conditions with AND and OR links in one query at the same time:
In this case, the sum of sales of Fanta in Abakan and Burn in Dubinin is calculated.
Sum by date range
In the range of conditions, you can easily set the date interval for the calculation. If the interval is limited on both sides, then you will need to set two conditions interconnected by a logical “AND” – therefore, you will have to write the conditions in one line, i.e. add another column with the same name Deal date:
In this case, the amount of sales is calculated Fanta for 2016 and fruittime until 2016 year.
Conditions for numbers
For selection by numerical criteria, you can safely use the usual inequality signs >, <, >=, <= as in regular Excel formulas. For example, if we need to sum up all sales of any type of cola, where the transaction amount was in the range of 500-600:
All but exceptions
If you need to exclude records by any parameter when summing up, then you can use the symbols “<>” denoting “not equal” in the Excel syntax. Suppose we need to sum up all the data for Fanta except for Samara and for Kvas except for Penza – it will look like this:
Note that if you need to sum Fant’u И Kvass all cities except Samara И Penza, then the design of the condition table will be slightly different – for each product, you need to exclude each city:
Conclusion
I hope you have already understood that the BDSUMM function is a very good tool and, often, a more convenient alternative to the classic selective counting functions like SUMMESLI (SUMIF) и SUMMESLIMN (SUMIFS). Also in the same category Working with the database (Database) you can find her “girlfriends” who calculate not only the sum:
- Count (DCOUNTA) – the number of non-empty cells (in our case, order lines)
- DMIN (DMIN) – minimal (for example, the worst deal)
- DMAX (DMAX) – maximum (best deal)
- SIGNIFICANT (DAVERAGE) — arithmetic mean (for example, average check)
They have the same syntax, and everything that was discussed above works exactly the same with these functions. So with this article we killed not one rabbit with you, but five 😉
- Selective calculations for one or more conditions
- Advanced filter and some magic
- Superfilter in VBA