# Comparing Lists of Values in Excel using Array Formulas

Posted on June 14th, 2011 in Excel Howtos , Learn Excel - 29 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.

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.

## 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.

## 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!

 Amount Donated vs. Pledged [Excel Formula Homework] Win Loss Chart from a Series of Win, Loss Data
 Written by Chandoo Tags: advanced excel, array formulas, countif(), countifs, downloads, Learn Excel, Microsoft Excel Formulas, sum(), sumif(), sumproduct, videos Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 29 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?

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

• Hui says:

@Vicktor
Is this the same question you asked in the other post ?
Please keep questions in one post or better off asking general questions in the Chandoo.org/Forums where more people answer them
Refer: http://chandoo.org/forums/

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

• Hui... says:

@Amy

I think you want something like:
=(1+INT((A2-0.00001)/500))*10
not an If() formula

20. Counting Sunday in date range says:

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

22. Akanyang Ntope says:

A student is doing 9 subjects but graded on only 7 subjects as outlined below
Sum of core subjects(English, Setswana,Maths,Science) +Best Optional(DT,Art,BS,HE,French,RE,PE)+2 best from remaining(Agric,Moral,Social, remaining option), so a student got the following points
English 5
Setswana 3
Maths 9
Science 1
Agric 9
Moral 7
Social 3
DT 9
RE 7

23. Sid says:

Hey Chandoo,

Long time listener, first time caller. I've been following you for years now. And taking a moment to genuinely thank you for the stuff you've put out here. It has helped me a lot professionally.

I never got around with Array formulae (despite working on excel for more than eight years now). I was never confident about them. Till I saw this example. Though my problem was little different this post and your video gave the key idea to go about it. And it worked!

In hindsight, if I'd applied a little extra attention + common sense I'd have learn't to manipulate array formulae long before. Anyhow, I'm starting now. Thanks to your example.

Thanks a lot for this. Helped a lot. And it's going to solve a lot of my issues.

Appreciate you for doing what you do.

God bless!

Regards,
Sid

• Hui... says:

@Sid

You may also want to have a browse through the Formula Forensics series
This series mostly deals with Array Formula
https://chandoo.org/wp/formula-forensics-homepage/

There are 47 examples so far
Here each example formula is pulled apart with details of how it works and how the array formula is structured to get the results we are after

• Sid says:

@Hui

Appreciate the revert (and the link). I shall definitely check it out.

Thanks again!

 Amount Donated vs. Pledged [Excel Formula Homework] Win Loss Chart from a Series of Win, Loss Data