sumif/sumifs to sum one row below criteria row

row A B
1 abc -1
2
3 def -1
4
5 abc
6 2
7 abc
8 -3
9 abc
10 1
11 def
12 4
13 abc -3

i have data in two different columns: column A has a string of letters only in odd rows and column B an associated number which is either in the same row or one below. i'm interested in finding the sum of associated numbers from column B given that the string in column A is "abc" - i.e. if A1="abc" i want to return the sum of B1 and B2.

i tried using =SUMIFS(B2:B13,A2:A13,"abc") but that only returns the sum of odd rows. how do i tell excel to look in cell A1 for "abc" and if it it returns B1+B2?

thanks in advance!

Formula:

Please Login or Register  to view this content.

Does that work for you?

For me, it's trial and error. I would say most of what I've learned, I've learned in this forum. I'd suggest browsing through the threads each day and, when you see something that either interests you or you think would be something you would use, watch that thread.

Glad I could help.