Sum Help

[dwgnome]

How can I use a formula to sum values below a certain word in a column. See example below where I need to sum all values just below the word dog:

[TABLE="width: 192"]
<TBODY>[TR]
[TD][/TD]
[TD]35</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]dog</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]54</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]34</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]33</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]dog</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]cat</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]cow</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]90</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]23</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total:</SPAN>
[/TD]
[TD]74</SPAN>
[/TD]
[TD]expected result for dog
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[NeonRedSharpie]

=IF(A1="dog",A2,0)

You can put this in a helper column (column B for example) and then sum based on column B. You could even make this modular by changing the code to:

=IF(A1=B$1,A2,0)

And then B1 will be "dog" or "cow" and can be changed. Or, if you want a custom UDF to ignore a helper column...

Function sumWordUnder(crit As Variant, rng As Range) As Long

    For Each cell In rng
        If cell = crit Then sumWordUnder = sumWordUnder + cell.Offset(1, 0)
    Next cell


End Function

The formula would read:

=sumWordUnder("dog",A2:A19)

 

[johnmpl]

Hi!

If your data is in A1:A18, try this formula (Confirm with CSE --> Control + Shift + Enter):

=SUM(IF(A1:A18="dog",A2:A19))

Or this (Without CSE):

=SUMPRODUCT(--(A1:A18="dog"),A2:A19)

Please comment! Blesings!

 

[Weazel]

another way

Excel 2010

	A	B	C
dog
dog
cat
cow

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]dog[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]74[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=SUMIF(A2:A11,C1,A3:A13)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dwgnome]

Thank you all, all of your approaches work. The only one I would not use is the helper column version as I don't like to use them when possible. The UDF worked as well, although I do not have the knowledge to make changes to such code, but i guess I can always ask the Board if I ever needed to do so.