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.
- 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 10 |
| 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 |
| 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. |
| 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. |
| 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. |
| 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!
29 Responses to “Comparing Lists of Values in Excel using Array Formulas”
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!
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!
Can't seem to open solution workbook in Excel 2003
@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!
@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!
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!
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.
@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.
@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. ""
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.
HI Chandoo, how could I see the entire video?. BTW, I am already enrolled into Excel school!
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.
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.
@Jim
What if the 25th day is a Saturday or Sunday ?
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.
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.
@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
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
@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/
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
@Amy
I think you want something like:
=(1+INT((A2-0.00001)/500))*10
not an If() formula
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
@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
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
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
I am using excel to calculate the total points for the 7 subjects to be graded, please help with the function
@Akanyang
Please ask this in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a sample file
You will get an answer fairly quickly
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
@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
@Hui
Appreciate the revert (and the link). I shall definitely check it out.
Thanks again!