Using array formulas can give you power to do what you can't do in normal sumif or sum formulas:
here is an examples
Consider the following list
Suppose you want to find out total deposit of people with age of 23, Female in business.
One way is to create a pivot and you can get the result but in that case you need to refresh again and again in case you change the data.
If you don't want to use pivot, array formulas come to your resort.
Have a look at following formulae
{=SUM(($A$2:$A$15=23)*($B$2:$B$15="F")*($D$2:$D$15="BUS")*C2:C15)} gives the result as 600
You must be wondering what curly bracket doing here. actually this the way excel recognise it as an array formulae and calculate it accordingly. For this you need to press cntl shift enter while entering the formulae instead of enter as you do in normal formulae.
Now let's try to understand how it works.
One can easily understand the criteria given in brackets like we are checking age 23 in column A, text F in column B etc.
What is different here is symbol * that is inserted between two brackets. This is used like an "and" argument between two criteria. Thus we are saying range $A$2:$A$15=23 and range $B$2:$B$15="F and $D$2:$D$15="BUS and sum range is C2:C15. This is similar to what we do while applying filter when we have more then one conditions.
similarly for OR argument we can use +
Trust me, it can be a powerful technique if you can use it efficiently
here is an examples
Consider the following list
| Age | Sex | Deposit | PROFILE |
| 23 | M | 500 | JOB |
| 23 | F | 600 | BUS |
| 24 | M | 600 | CONSULTANT |
| 25 | F | 600 | JOB |
| 24 | M | 600 | BUS |
| 24 | F | 600 | CONSULTANT |
| 23 | M | 800 | JOB |
| 23 | M | 800 | BUS |
| 24 | F | 800 | CONSULTANT |
| 23 | M | 800 | JOB |
| 24 | F | 800 | BUS |
| 23 | F | 400 | CONSULTANT |
| 24 | F | 400 | JOB |
| 23 | M | 400 | BUS |
Suppose you want to find out total deposit of people with age of 23, Female in business.
One way is to create a pivot and you can get the result but in that case you need to refresh again and again in case you change the data.
If you don't want to use pivot, array formulas come to your resort.
Have a look at following formulae
{=SUM(($A$2:$A$15=23)*($B$2:$B$15="F")*($D$2:$D$15="BUS")*C2:C15)} gives the result as 600
You must be wondering what curly bracket doing here. actually this the way excel recognise it as an array formulae and calculate it accordingly. For this you need to press cntl shift enter while entering the formulae instead of enter as you do in normal formulae.
Now let's try to understand how it works.
One can easily understand the criteria given in brackets like we are checking age 23 in column A, text F in column B etc.
What is different here is symbol * that is inserted between two brackets. This is used like an "and" argument between two criteria. Thus we are saying range $A$2:$A$15=23 and range $B$2:$B$15="F and $D$2:$D$15="BUS and sum range is C2:C15. This is similar to what we do while applying filter when we have more then one conditions.
similarly for OR argument we can use +
Trust me, it can be a powerful technique if you can use it efficiently
