SUM/IF Forumlas

[mgoldie1]

This is probably pretty easy, but I can't figure it out. Obviously I can SUM a column. However, I want to SUM the next column (Column D), but I'm trying to SUM column C but ignore any entries that have certain words from Column A (Mom, Dad, and Lisa). How do I put that in a formula for cell D13. Thanks.

Marc

https://www.dropbox.com/s/ffa5lep3otiqf2o/Sum Test.xlsx?dl=0

 

[Scott Huish]

I think the easier way to look at that is that you are only trying to sum the cells that are Free or Bonus:

=SUM(SUMIF(A3:A12,{"Free","Bonus"},D3:D12))

 

[wist]

definitely easier what Scott mentioned but if you only know what words not to include then this should work

=SUMIFS(B2:B12,A2:A12,"<>Mom",A2:A12,"<>Dad",A2:A12,"<>Lisa")

 

[jtakw]

Hi,

Assuming OP also wants the "un-categorized" SUMed also:

Book1
	A	B	C	D
1	Types		Totals	Totals Filtered
2			4
3	Bonus		8
4	Mom		5
5	Dad		2
6	Lisa		1
7			12
8	Free		5
9			2
10	Bonus		3
11			2
12	Mom		2
13			46	36
Sheet1

Cell Formulas
Range	Formula
D13	=SUMIFS(C2:C12,A2:A12,"<>Mom",A2:A12,"<>Dad",A2:A12,"<>Lisa")

 

[mgoldie1]

Thanks everyone. That helps a lot!!

Hi,

Assuming OP also wants the "un-categorized" SUMed also:

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Types[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Totals[/TD]
[TD="align: center"]Totals Filtered[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Bonus[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Mom[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Dad[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Lisa[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]Free[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]Bonus[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]Mom[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]46[/TD]
[TD="align: center"]36[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D13[/TH]
[TD="align: left"]=SUMIFS(C2:C12,A2:A12,"<>Mom",A2:A12,"<>Dad",A2:A12,"<>Lisa")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

You're welcome.