Example of SUMPRODUCT function in Excel used for an example from Social Sector.
Let us assume, that as a funding company your donations for a month are as per the table given below.
Amount in Rupees | Description |
25,000 | Covid Relief - PPE kits |
10,000 | Covid Relief - Grocery kit |
40,000 | Covid Relief - Grocery kit |
20,000 | General Donation for healthcare activities |
44,000 | Medical Assistance |
3,50,000 | Covid relief activities |
10,000 | General donation |
6,000 | Monthly aid |
89,600 | 50% advance for supply of medicines |
5,00,000 | Covid relief activities |
4,980 | Supply of groceries to tribal children |
5,00,000 | Relief activities for Covid |
49,450 | Scholarship |
22,166 | Scholarship |
Your manager wants to know the spend on covid relief related activities for that particular month.
Now let us find out how SUMPRODUCT function can do the conditional sum. The Excel SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.
The expression to use is =SUMPRODUCT(Table1[Amount]*--(ISNUMBER((SEARCH("covid",Table1[Purpose],1))))). Where the dataset has a name of Table1.
If the term covid is found, the search function returns a number indicating its position. ISNUMBER function then would return True. The double negative forces True and False to 1 and 0 for doing the multiplication. Then the addition.
=SUMPRODUCT(Table1[Amount]*(ISNUMBER((SEARCH("COVID",Table1[Purpose],1))))) is similar to the above formula.
Thus, to recap, wherever the word covid is found, the function returns “True”, and the corresponding amount is multiplied by 1. All such amounts are added. The search term “covid” is not case sensitive. You can use “COVID” as well. The answer is Rs 14,25,000.
Similarly formula =SUMPRODUCT(Table1[Amount]*--(ISNUMBER((SEARCH("Scholarship",Table1[Purpose],1))))) can be used to calculate donations given as Scholarships. The answer is Rs 71,616.
In my next blog, I will add another dimension of time to calculate the conditional sum.
13:26 01-09-2021
***