## Comparing List of Values – Different Scenarios

There are 2 scenarios when we compare lists of values.

- Comparing a list of values with a single value (or condition)
- Comparing a list of values against another list (array comparison)

## Comparing a list of values with a single value (or condition):

This is the most common and easiest comparison. Examples of this are – count of all values > 20, sum of values between 5 and 23, count of employees in purchasing department etc.

We have built in formulas in Excel to help us do this easily.

Formula |
What it does? |

COUNTIF | Counts all the values in a range that meet a criteria. Example: COUNTIF(A1:A10,”>10″) Count of all values in A1:A10 more than 10Help |

SUMIF | Sums all the values in a range that meet a criteria Example: SUMIF(A1:A10,”>10″,B1:B10) Sum of all values in B1:B10 where corresponding value in A1:A10 is more than 10Help |

COUNTIFS* | Counts all the values in a range that meet multiple criterion Example: COUNTIFS(employees, “a*”,departments, “Purchasing”) Counts the number of employees in Purchasing department whose name starts with letter a.Help |

SUMIFS* | Sums all the values in a range that meet multiple criterion Example: SUMIFS(salaries, employees, “a*”,departments, “Purchasing”) Sums up the salary of employees in Purchasing department whose name starts with letter a.Help |

SUMPRODUCT | Gives the sum of product of various lists. This formulas is very robust and can be used to compare lists and check against multiple conditions Example: SUMPRODUCT(salaries, departments=”Purchasing”, join_date>datevalue(“1-May-2009″),join_date<=datevalue(“1-May-2011″)) Sums up the salary of employees in Purchasing department who joined between 1-May-2009 and 1-May-2011.Help |

AVERAGEIF* | Average of all the values in a range that meet a criteria Example: AVERAGEIF(A1:A10,”>10″,B1:B10) Average of all values in B1:B10 where corresponding value in A1:A10 is more than 10 |

AVERAGEIFS* | Average of all the values in a range that meet multiple criteria Example: AVERAGEIFS(salaries, employees, “a*”,departments, “Purchasing”) Average salary of employees in Purchasing department whose name starts with letter a. |

* these formulas do not work in Excel 2003 or earlier versions.

## Comparing a list of values with another list (array compare):

This is where it gets interesting. You have 2 lists of values, like in our last week’s problem. And you want to calculate some value, for eg. Sum of all donations where Amount Donated < Amount Pledged.

How do you go about this?

**Well, this is where we use Array Formulas.**

In the above case, assuming we have amount donated in lstGiven and amount pledged in lstPledged,

We can use the array formula =SUM((lstGiven)*(lstGiven<lstPledged)) to find the sum of all donations such that amount donated is less than amount pledged.

Note: You must press CTRL+SHIFT+Enter to get this formula work

### How does this formula work?

- The formula checks for lstGiven < lstPledged and returns a bunch of TRUE, FALSE values.
- When you multiply this with lstGiven, Excel would convert TRUE, FALSE to 1 and 0 and then multiply.
- Since 0 multiplied by anything would 0, we end up with a bunch of donation values where donated amount is less than pledged amount.
- Once all the values are there, the SUM would just add them up.

## More examples & Illustration:

Look at below image to understand how we can compare lists of values in Excel using Array formulas.

## Solution to Donation Summary Calculation Problem:

I have made a video explaining how you can solve the last week’s homework. See it below or on our Youtube Channel.

## Download the Excel Workbook for this.

**Click here to download the Workbook with partial solution as shown in the video.**

Click here to download the solution workbook and play with the formulas to learn more.

## Share your tips on Array Formulas

Array formulas are quite powerful and robust. I use them all the time and love to learn more. *So please share your tips and ideas using comments. Go!*

from: http://chandoo.org/wp/2011/06/14/compare-lists-array-formula/