Comparing Lists of Values in Excel using Array Formulas

Posted on June 14th, 2011 in Excel Howtos , Learn Excel - 24 comments

Last week, we had a home work on Calculating Donation Summaries using Excel Formulas. This is a good case where array formulas can help us. So today, we will learn how we can use Array Formulas to compare lists of values and calculate summaries. Towards the end of this post, you can see a video that explains the solution to Donation Summary Calculation problem.

Comparing List of Values – Different Scenarios

There are 2 scenarios when we compare lists of values.

  1. Comparing a list of values with a single value (or condition)
  2. 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 10 

Help

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 10 

Help

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?

  1. The formula checks for lstGiven < lstPledged and returns a bunch of TRUE, FALSE values.
  2. When you multiply this with lstGiven, Excel would convert TRUE, FALSE to 1 and 0 and then multiply.
  3. Since 0 multiplied by anything would 0, we end up with a bunch of donation values where donated amount is less than pledged amount.
  4. 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.

Array Formulas to Compare Lists in Excel - Examples

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!

Learn More about Excel Array Formulas:

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

24 Responses to “Comparing Lists of Values in Excel using Array Formulas”

  1. charles jackson says:

    Arrays formulas in excel are powerful, and it is mainly used by people with advance knowledge in excel.
    @chandoo, could you please throw light on using logical OR too in array function? For example if I have a data with name, department, gender, age and salary as headings, and I want to count the number of people who are male (m) or aged less than 25.
    =sum(if((gender="m")+(age>25),1,0))
    and press CTRL+SHIFT+Enter keys at the same time.

    Array formulas are handy!

  2. juanito says:

    I used SUMPRODUCT, which is pretty much the same as Chandoo's method (SUMPRODUCT is effectively the same as array-entered SUM, although perhaps easier to start off with for people who are a little put off by array formulas).
    I find this sort of technique extremely useful and recommend all would-be ninjas to master it!

  3. Rich says:

    Can't seem to open solution workbook in Excel 2003

  4. Hurls says:

    @juanito - I completely agree. I'm surprised @chandoo choose SUM instead of SUMPRODUCT. With the latter, you don't have to worry about the Ctrl + Shift + Enter.
    .
    Thanks for this post because I'm shocked I never knew about SUMIFS, COUNTIFS, and AVERAGEIFS. Very cool!

  5. Prem Sivakanthan says:

    @Charles I'd use SUMPRODUCT for your scenario, e.g. SUMPRODUCT((gender="m")+(age10,MyData)), 5)} of course ctrl+shift+enter to get the curly brackets. Long live the array formula!

  6. Prem Sivakanthan says:

    Not sure what happened with my post above, but here it is again:

    @Charles I'd use SUMPRODUCT for your scenario, e.g. SUMPRODUCT((gender="m")+(age10,MyData)), 5)} of course ctrl+shift+enter to get the curly brackets. Long live the array formula!

  7. Prem Sivakanthan says:

    grrrrr! is it only me or is something wrong with this comment board, its seems to be cutting off my posts. Please ignore the above two posts - it has mixed up what I was trying to say...I'll try posting later on.

  8. Hurls says:

    @Prem, I think it interprets anything between a LT sign and a GT sign as an HTML tag. I'm guessing it's not a valid tag, so it just doesn't show it.

  9. Prem Sivakanthan says:

    @Hurls thanks, is there a way around it? I use the LT and GT signs in my forumlas extensively - if I was to enclose the signs in "quotes" would it still show? e.g. ""

  10. Prem Sivakanthan says:

    okay, here is my post, third time lucky with no LT or GT signs...

    @Charles I'd use SUMPRODUCT for your scenario, e.g. SUMPRODUCT((gender="m")+(age"lowerthan"25)).I use SUMPRODUCT all the time to get run very specific queries when mining data. However, if you are using it on a very large dataset it does have an impact on speed. I worked around it by replacing SUMPRODUCT with SUMIFs.I often use the IF function array entered. For example, I might have a dataset (named MyData) and want to conditionally sort it. For example, show me the top 5 items in MyData where the values are more than 10, this formula would do the trick:=LARGE(IF(MyData"greaterthan"10,MyData)), 5) of course ctrl+shift+enter to get the curly brackets.

  11. SAUL says:

    HI Chandoo, how could I see the entire video?. BTW, I am already enrolled into Excel school!

  12. Prem Sivakanthan says:

    While I'm on a roll spamming this thread...how about using SUM array entered to create a RANKIF function? I have two named ranges, one called student (contains a range full of student names) and another called scores (contains the corresponding scores for each student for a range of tests that they have completed during the year). How do I rank each students scores in comparison to ONLY their previous scores? With an array formula of course! Assuming the scores are in column B: = SUM((student="insertnamehere")*(scores>B2))+1...+ CSE and wallah you have a conditional rank. Of course you can replicate this with the super dooper sumproduct as well - and in doing so you'd be able to get some pretty impressive and specific ranks.

  13. Jim says:

    I need a formula that can pull certain rows within a large report. This is what I have... A spreadsheet showing daily returns of the S&P going back to 1950. I need to pull every 25th day of every month of every year. The date format is 1/25/2001.

  14. Hui... says:

    @Jim
    What if the 25th day is a Saturday or Sunday ?

  15. Jim says:

    Good Morning Hui, that is something that I should of posted as well. It would then be the following Monday that would be used. Thank you for bring that up.

  16. Bill L says:

    This is great. I have a related question... Anyone know how to compare two sets (ranges of values) and get the overlap (intersection) of the sets? I'm trying to get a list of values, not the average or sum as described in this post.

  17. Hui... says:

    @Jim
    Is this a one off or do you want to do it regularly?

    As you can add a Column to your data
    I assume you have Date in Column A

    Put a formula in the new column Row 3: =IF(DAY(A3)=25,1,IF(AND(DAY(A3)>25,DAY(A2)<25),1,0))
    Copy down
    Copy & Paste new Column as Values
    There will now be a 1 in all rows where it is the 25th or next day.
    Sort by the New Column and Date Column
    Voila

  18. vicktor says:

    I am trying to substract the abs value first row - row2, row1-row3 etc, and when finish with all, start over with the row2-r3, r2-r4 etc, etc 
    my database is A1:F1900 any macro-vba-code for this.
     

    13
    26
    50
    51
    52
    53
    245
    row#1
    11
    15
    9
    7
    8
    8

    2
    7
    18
    29
    35
    44
    135
    row#2
    8
    19
    40
    27
    24
    10

    5
    7
    10
    24
    28
    43
    117
    row#3
    9
    18
    30
    27
    17
    10

  19. Amy says:

    Hi, I m in need of excess formula which will compair the value of particular cell and give the result. i.e. if value of cell is between range =<500, result should be 10, if value is =<1000 result should be 20 and so on. 
    Could u plz suggest me the formula.
    Thks 

  20. Counting Sunday in date range says:

    Please help answer this:

    How can i count number of sunday in the range of dates for example from 04/1/2013 to 04/30/2013
    and please explain how it happen,

    thanks

    • Hui... says:

      @Counting Sunday
      I'd use =NETWORKDAYS.INTL(B2,B3,"1111110")

      Where:
      B2 has the Start date
      B3 has the End date

      "1111110" is used to indicate which days in Monday to Sunday are workdays
      1 is a Non-Work Day
      0 is a Work Day

      The Networkdays.Intl() function only works in Excel 2007+

      If your using an Older version of Excel use:
      =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B2&":"&B3)))=1))
      Where 1 = Sunday .. 7 = Saturday

  21. Rob says:

    Does anyone have any idea how I can get a list of values that match any of the values in another array? For example, if I had a large table with cities and values and I wanted to create an average of the values where cities = Array_X, where array_X is a range with about 20 cities listed in it?

    Thanks
    Rob

Leave a Reply