sumif with multiple conditions

[Mr2017]

Hi

I'd like to create a sumifs formula which sums up negative values only.

I've created a simple example below, where I've written a sumif which sums up the values for the name "Matthew" in column B (you can copy and paste the data below into a new workbook - it starts in cell A1).

There are THREE instances of the name Matthew, with values, -3, -3 and 2. At present the sumifs returns a value of -4 because it's summing up all values (positive and negative).

However, I'd like it to only sum up the negative values and return -6. Does anyone know how to do this, please?

Thanks in advance.

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Name[/TD]
[TD="class: xl63, width: 64"]Value[/TD]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 64"]Sumifs[/TD]
[/TR]
[TR]
[TD="class: xl63"]Matthew[/TD]
[TD="class: xl63, align: right"]-3[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]-4[/TD]
[/TR]
[TR]
[TD="class: xl63"]Mark[/TD]
[TD="class: xl63"] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Luke[/TD]
[TD="class: xl63"] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]John [/TD]
[TD="class: xl63"] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Matthew[/TD]
[TD="class: xl63, align: right"]-3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Mark[/TD]
[TD="class: xl63"] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Luke[/TD]
[TD="class: xl63"] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]John [/TD]
[TD="class: xl63"] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Matthew[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Mark[/TD]
[TD="class: xl63"] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]Luke[/TD]
[TD="class: xl63"] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]John [/TD]
[TD="class: xl63"] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

The SUMIFS formula allows for multiple conditions. So just add another condition checking for the value "<0".
See here if you aren't sure of the structure of SUMIFS: https://www.techonthenet.com/excel/formulas/sumifs.php

 

[Mr2017]

Ok, thanks.