When we have a large dataset, we can sum specific criteria met data in a lot of different ways. One of them, is SUMIF, a function that returns a criteria-based sum of data.
SUMIF function requires three parameters to be fully defined. Let's take a look at the example below.
A | B | C | D | E | F | |
ID | DATE | CATEGORY | INVOICE | CUSTOMER | TOTAL | |
1 | QA010 | 2/1/2021 | Electrical | ZA267QG | Raymont | 1747,18 |
2 | QA011 | 13/3/2021 | Heating | ZA132QG | Gazolina | 1900,30 |
3 | QA012 | 18/3/2021 | Electrical | ZA125QG | Raymont | 1717,05 |
4 | QA013 | 12/2/2021 | Heating | ZA325QG | Bliss Co | 2641,49 |
5 | QA014 | 7/1/2021 | Tools | ZA66QG | Gazolina | 2346,08 |
6 | QA015 | 3/2/2021 | Electrical | ZA288QG | Gazolina | 197,03 |
7 | QA016 | 8/2/2021 | Tools | ZA247QG | Merch sa | 1231,90 |
We need for a specific CUSTOMER the total amount of all his/hers invoices.
So, let's say that we want the result to J6 and we will use J3 as the criteria cell.
Note: DO NOT USE as criteria a cell reference inside the data table. It is not a good practice.
Range is the column that the criteria will be applied to. In our case it's CUSTOMER
Criteria: Is the Cell J3 where we are going to type one of the customers name.
Sum_Range: Is the TOTAL column that holds the arithmetic values.
Check out the Video:
Don't forget to subscribe to our channel Excelixis