Sumif for category and criteria

[matty_lou82]

Hi there,

I am looking to create a sumif (or sumifs) for the following data set, so that a sum is given for each name, HOWEVER with the caveat of the addioanl criteria of under a certain value. For example, the total sum of volume for Matt, but only for values less than 4.

Please can someone help, it would be really appreciated.

[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"]Volume[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Matt[/TD]
[/TR]
[TR]
[TD]Stu[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]Stu[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]Chris[/TD]
[/TR]
[TR]
[TD]Andy[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]Andy[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Carl[/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stu[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Andy[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stu[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Andy[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stu[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Andy[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stu[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Andy[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stu[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Andy[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stu[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stu[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stu[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63"]TOTAL[/TD]
[TD="class: xl63, align: right"]179[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Assuming Name is in column A, volume is in column B, and the name "Matt" is in cell D2, put this formula in E2 and copy down for the other names:

=SUMIFS(B:B,A:A,D2,B:B,"<4")

 

[matty_lou82]

Thank you so much! Very helpful!

 

[Joe4]

You are welcome.