Multiple Conditions


Performing Calculations with Multiple Criteria

Lets suppose you have a table of information and you want to obtain some summary information.
This can be quickly achieved using array formulas.
There are 2 formulas which you can use for this and both will give you the same result.
We are going to start with the slightly shorter formula.


 

The formula is cell "D15"


The second formula is the one generated by the Add-ins > Conditional Sum Wizard.
This one might be easy to understand as it uses the IF function.


 

The formula is cell "D15"


COUNT and SUM Functions

You can use the COUNTIF and SUMIF() to quickly return the number of cells or the total value for a given criteria.
It is possible though to obtain the same information just by using the regular SUM function with array formulas.


 


COUNT Matching Rows

You can use the COUNTIF to return the number of cells that satisfy a given criteria.
Alternatively you can also use the SUM function with array formulas.


What is the total number of transactions where Quantity = 5

{=SUM(1*(D3:D20=5))} = 6


What is the total number of transactions where Quantity = 5 or Quantity = 10

{=SUM((D3:D20=5)+(D3:D20=10))} = 12


What is the total number of transactions where Quantity = 5 and Location = New York

{=SUM((D3:D20=5)*(B3:B20="New York"))} = 2


SUM Matching Rows

You can use the SUMIF to add the cells specified by a given criteria.
Alternatively you can also use the SUM() function with array formulas.


What is the total price for all the transactions where Quantity = 5

{=SUM((D3:D20=5)*E3:E20)} = 14,886


What is the total price for all the transactions where Quantity = 5 or Quantity = 10

{=SUM(((D3:D20=5)+(D3:D20=10))*E3:E20)} = 38,647


What is the total price for all the transactions where Quantity = 5 and Location = New York

{=SUM(((D3:D20=5)*(B3:B20="New York"))*E3:E20)} = 2,485

Copyright © 2001 - 2016 Better Solutions Limited | All Rights Reserved.    Previous

    Next