Excel tutorials

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'.

How to sum values from one column for multiple unique criteria

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.

Sum values for multiple conditions if match unique text from two columns

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).

Return sum from one column if match multiple criteria

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.

Sum values for unique from two column with one criteria

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.