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 Davis. 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:
=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 Medical. Here's the example:
=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.