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:

	35</SPAN>
	dog</SPAN>
	54</SPAN>
	34</SPAN>
	33</SPAN>
	12</SPAN>
	dog</SPAN>
	20</SPAN>
	cat</SPAN>
	12</SPAN>
	cow</SPAN>
	90</SPAN>
	23</SPAN>
Total:</SPAN>	74</SPAN>	expected result for dog

<TBODY>
</TBODY>

 

[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
1	35		dog
2	dog		74
3	54
4	34
5	33
6	12
7	dog
8	20
9	cat
10	12
11	cow
12	90
13	23

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =SUMIF(A2:A11,C1,A3:A13) <tbody> </tbody>

<tbody>
</tbody>

 

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