22 November, 2021

SUMMING UP selectively based on references - using ARRAY FORMULAS

Some business sells some products, e.g. vegetables, to various countries/ merchants. Records of sales are kept in Excel table, like in this simple example:

The business owner needs to keep his eye on total sales of individual vegetables over some periods. He could do necessary calculations by e.g. filtering the specific column and summing up individual rows. However, much more efficient way of doing it - in such a case - is using ARRAY formulas, as you can see below.

The totals of sales by the product, over the three months (cols. C, D and E), are calculated by formulas underlying the values presented in col. H and presented on the ride side of that column:

If needed, similar formulas could be used to sum up the sales by the country (col. B) or other references to the table entries.

ARRAY formulas, after typing them in, must be entered using the keyboard sequence CTRL+SHIFT+ENTER. Note also the double-dash operator used in the formulas.

No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.