Let's assume that you are using Excel table for recording sales of some products by several agents. From time time you need to check how your business is doing.

The table is getting larger and larger. I set its size arbitrarily to 1000 rows, but this is up to the user needs. Here is just its small fragment:

One day you may need to know, e.g., the **total value** of __ Western__ items sold by

__. What kind of formula would you use to obtain the value quickly and automatically? Well, in such a case I'd recommend an array formula like this one:__

*Davis***=SUM((Sheet3!A2:A1000="Western")*(Sheet3!B2:B1000="Davis")*(Sheet3!C2:C1000))**

The answer you'd get is **1900**.

Similar formula could be used, e.g., to **count*** *the number of all sales in the table, **except for those** in which __ Davis__ sold the

__. Here's the example:__

*Medical***=SUM(IF((Sheet3!A2:A1000="Medical")+(Sheet3!B2:B1000="Davis")<>2,1,0))-COUNTIF(Sheet3!A2:A1000,"")**

The answer would be equal to **20**.

Based on these examples, a number of similar formulas can be created to make summing and counting immediate and effortless, for many real needs and situations.

** Remember**: these are array formulas, so - after typing in - they must be entered using simultaneously

**CTRL+SHIFT+ENTER**keystrokes; otherwise you may get an error or wrong result.

## No comments:

## Post a Comment

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