Adding Values in One Column for Multiple Conditions
If you ever needed the SUM of values in a one column that contains multiple criteria, in this Excel tutorial I will show an example of formulas how can we sum up of values from one column to multiple criteria. If you look at the picture below the situation is the following. In columns 'B' and 'D' you can see the text that we want the sum of values from one column and these are columns 'C' and 'E'. Also note that in columns 'B' and 'D', duplicate text is found. Our task is the sum of values for the terms 'letter A' and 'letter M'.
Use the formula below for the sum of values with multiple conditions in one column. Note that the criteria or conditions are defined in the formula itself.
To sum the values from column 'C' (Header2) for the two letter 'A and M' then we can use the formula.
=SUM(SUMIF(B3:B6;{"A";"M"};C3:C6))
To make the sum of the values in column 'E' (Header4) for the two letter 'A and M' then we can use the formula.
=SUM(SUMIF(D3:D6;{"A";"M"};E3:E6))
Sum of values for single columns with multiple conditions
If we want to find unique text data from two columns and add values from one column then we can do this in the following way.
In the 'G' column below, you can see unique data from two columns below. This data can be obtained from columns 'B and D' by the following formula below the image.
Return Unique Text if there are Duplicates in Two Columns
The array formula shown below is in cell 'G3' and it is next. (This formula will return 'A and M' to two unique letters because they are repeated in both 'B' and 'D' columns).
=IFERROR(INDEX(D:D;SMALL(IF(ISNUMBER(MATCH(D$3:D$6;B$3:B$6;0));ROW(D$3:D$6));ROWS(G$3:G3)));"")
Next formula in 'H3' cell
This formula will return the value from the same row for condition in 'G3' cell. Copy a formula down.
=SUMIF($D$3:$D$6;G3;$E$3:$E$6)
In 'H8' cell we have a simple formula for summation (SUM)
=SUM(H3:H7)
The formula in 'G13' cell is as follows: (Another formula that is related to cell range 'G3:G7')
=SUMPRODUCT(C3:C6;--ISNUMBER(MATCH(B3:B6;$G$3:$G$7;0)))
Sum of Values for Multiple Conditions from One Column
If you define the conditions in certain cells (in this case, in the picture below, there are 'J3' and 'M3' cells, then you can use the formula below the picture (All the formulas below show the same result).
The formula in 'L5' cell is the following:
=SUMPRODUCT((J3=B3:B6)*(C3:C6)+(K3=B3:B6)*(C3:C6))
The formula in 'L7' cell is the following:
=SUMPRODUCT(((B3:B6=J3)+(B3:B6=K3));C3:C6)
The formula in 'L9' cell is the following:
=SUMPRODUCT((B3:B6=J3)*(C3:C6))+SUMPRODUCT((B3:B6=K3)*(C3:C6))
The formula in 'L11' cell is the following:
=SUMPRODUCT(C3:C6;--ISNUMBER(MATCH(B3:B6;$J$3:$K$3;0)))
The formula in 'L15' cell is the following:
=SUMIF($B$3:$B$6;J$3;$C$3:$C$6)+SUMIF($B$3:$B$6;K$3;$C$3:$C$6)
The formula in 'L17' cell is the following: (here, please note that this formula has three conditions, the third condition is 'Header2' in 'L2' cell.
=SUMIF($B$3:$B$6;J$3;INDEX($C$3:$C$6;0;MATCH(L$2;C2:E2;0)))+
SUMIF($B$3:$B$6;K$3;INDEX($C$3:$C$6;0;MATCH($L2;$C2:$E2;0)))
Sum of Values for Unique from Duplicate Data with Criterion <>0
In case you have a situation when you want to set a condition or criterion then you can use the auxiliary columns as shown below.
In the column 'F' in the image above, you find the auxiliary data for the 'Header1' column. In the column "G" in the image above you will find the auxiliary data for the 'Header3' column. Note that there is no 'A' in the column 'F'. If you're wondering why? Because the value for that data in column 'B' = 0 is the condition we set in the formula.
The formula in 'F2' cell is the following:
=IF(VLOOKUP($A2;$A$2:$B$5;2;FALSE)=0;"";A2)
The formula in 'G2' cell is the following:
=IF(VLOOKUP($C2;$C$2:$D$5;2;FALSE)=0;"";C2)
In column 'H', unique data for duplicates from columns 'A' and 'C' are extracted but only those that do not have the corresponding zero value '0'.
ARRAY CSE Formula in 'H2' cell is the following:
=IFERROR(INDEX(G:G;SMALL(IF(ISNUMBER(MATCH(G$2:G$5;F$2:F$5;0));ROW(G$2:G$5));ROWS(H$2:H2)));"")
And finally the Array CSE formula in 'H8' cell is the following:
=SUMPRODUCT(D2:D5;--ISNUMBER(MATCH(C2:C5;H2:H5;0)))
If you want to avoid helper columns then using the following ARRAY formula (published by Andrej)
=SUM(IF(COUNTIF(A2:A9;C2:C9)=1;D2:D9);0)-SUM(IF(COUNTIF(A2:A9;C2:C9)=1;IF(SUMIF(A2:A9;C2:C9;B2:B9)=0;D2:D9;0);0))
or
=SUM(IF(COUNTIF(A2:A5;C2:C5)=1;IF(SUMIF(A2:A5;C2:C5;B2:B5)>0;D2:D5;0);0))
Please note when using the SUMPRODUCT Excel function. If you have lots of rows and data or if you have a lot of formulas that contain the Sumproduct function, then it can slow you down when calculating Excel.
Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.