How To Search And Sum Multiple Criteria In Excel
Yous may need to summate the sum of multiple columns. In one case you know the methods it will seem as easy as y'all like. The agenda for today is showing you how to sum multiple columns based on multiple criteria.
First things first, let'south get to know about today'due south practice workbook
We take a relationship table of suppliers and their corporeality of sales made within three months in different cities.
This table contains dummy data. Things to be noted, this is a basic table, in existent-life scenarios you may run into many complex tables.
Practice Workbook
You are welcome to download the workbook from the link below.
Sum Multiple Columns Based on Criteria
Based on Unmarried Criteria
1. Using SUMIF function
If yous have a single criterion then it volition be a walk in the park for y'all while computing the sum. All you need to do is use multiple SUMIF functions within OR logic.
SUMIF(range,criteria,sum_range1)+SUMIF(range,criteria,sum_range2)+...
Hither " +" works as OR logic. Each of the SUMIF functions produces a result and then the final consequence comes adding them together.
Write the part in Excel.
Here we calculated the sum from multiple columns where our criteria was the supplier Sunrise Wholesale.
2. Using SUMPRODUCT
We can go the sum using the SUMPRODUCT function likewise. If yous want to know virtually the function broadly, delight visit the Microsoft Support site.
The formula will exist
SUMPRODUCT((criteria_range=criteria)*sum_range)
(criteria_range=criteria) returns an array of True and Faux. TRUE for match and Fake for not lucifer. And then gets multiplied with sum_range.
Write the formula in Excel.
We have shown you an instance with supplier BryBelly as our criteria. And the formula provided the consequence we wanted.
Based on Multiple Criteria
1. Using SUMIFS function
If you have heard about the SUMIFS function, then information technology's obvious that it comes first to your mind while summing based on multiple criteria.
For a quick recap, the SUMIFS function allows you to sum cells that match multiple conditions.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], …)
sum_range – The range to be summed.
criteria_range1 – The showtime range to evaluate.
criteria1 – The criteria to use on range1.
criteria_range2 – The 2d range to evaluate.
criteria2 – The criteria to apply on range2.
criteria_range2 and criteria2 are optional fields. If you lot take multiple ranges/criteria then utilize these fields. And you can insert as many criteria as you want.
Read more than: SUMIF Multiple Ranges
Let, nosotros have two criteria, Supplier and Urban center. We need to sum the corporeality that matches our criteria.
Since we are aiming to employ conditions based on Supplier and City, let'southward choose any of the suppliers and cities.
Here we have selected Sunrise Wholesale (Supplier) and New York (Metropolis). Now apply the SUMIFS formula counting these two equally our criteria.
Since nosotros intend to find the total amount, our sum_range needs to be the whole. Then, we inserted D4:F21 equally sum_range.
Supplier Proper name column as criteria_range1 and City column equally criteria_range2.
Our formula didn't work perfectly. It returned an error value. Exercise you know why?
The SUMIFS function doesn't work for a multi-column sum_range. We provided the sum_range D4:F21. The range consists of multiple columns. And so the formula didn't piece of work.
Now, what can we practice?
We need to break down the sum_range. You may take heard – Carve up and Conquer. Divide circuitous things into pocket-size simpler issues and and so apply operations.
Nosotros will exercise the aforementioned hither. Nosotros will dissever the multi-column sum_range into a single column.
Our singled column sum_range will accept part within the SUMIFS function. And to count all the columns we demand to apply multiple SUMIFS functions.
And then our formula will be
SUMIFS ( sum_range1, criteria_range1, criteria1, [criteria_range2], [criteria2], …) + SUMIFS ( sum_range2, criteria_range1, criteria1, [criteria_range2], [criteria2], …)+ …
Write the formula in Excel.
Here we have written 3 SUMIFS functions. Within each SUMIF office, we accept provided a single column sum_range. And it worked.
It gave the total Amount Sale by Sunrise Wholesale in New York city. Go through the data sets, yous will discover the total matches that our formula returned here.
2. Using SUM Function
Nosotros tin can count the sum based on multiple criteria using the SUM function.
We are choosing Supplier and Urban center every bit our criteria for showing examples.
Here, Spare Vally and Los Angeles are our two example criteria.
Our SUM formula for multiple criteria will exist something like
SUM((column_range1+column_range2+..)*(--(range1=criteria1))*(--(range2=criteria2))*..)
Write the formula in Excel.
Here we have inserted the three summing columns separating by "+". This will give an array of a sum past each row.
(criteria_range = criteria) returns an array of True and Simulated. Two unary operators (--) catechumen this array into an array of 1s and 0s.
We have included the criteria_ranges and criteria by "*". This worked as And role and multiplied the arrays.
Then the SUM function provided the result. Since information technology deals with arrays you need to use CTRL + SHIFT + ENTER to execute the formula.
Watch out for another example using BryBelly and San Fransisco as criteria
iii. Using SUMPRODUCT function
We can do the task past SUMPRODUCT function every bit well. Our generic formula using the SUMPRODUCT volition exist something similar below
SUMPRODUCT((criteria_range1=criteria1)*(criteria_range2=criteria2)*..*(sum_range))
(criteria_range=criteria) returns an assortment of TRUE and False. TRUE for match and Faux for not match.
And then all these arrays get multiplied together. And forms another array. This array then gets multiplied with the sum_range array.
Then SUMPRODUCT role sums the element of the assortment and produces the result.
Permit's see an example using this formula.
Nosotros are choosing Alibaba.co and West Hollywood as our criteria conditions.
Write the formula in Excel.
It gave the result we accept wanted. Yous can go through the dataset and check whether the formula gave the right result or not.
Let's run across an example using more two criteria. We have selected Supplier, Sales Person and City equally our criteria.
Write the formula for this example.
It gave the total amount of sell Mary did in New York city for Sunrise Wholesale.
Earlier going into the real scenario do some practice of your own.
Decision
That'due south all for the article today. Nosotros accept listed several formulas to sum multiple columns based on multiple criteria. Hope you lot will detect this helpful. Feel free to comment if annihilation seems difficult to understand. Let us know any other formula or methods which we might have missed here.
Further Readings
- Sum Based on Column and Row Criteria in Excel
Source: https://www.exceldemy.com/sum-multiple-columns-based-on-multiple-criteria/
Posted by: parkreakes2000.blogspot.com
0 Response to "How To Search And Sum Multiple Criteria In Excel"
Post a Comment