Hi,

I'd be immensely grateful if you could help me with the following conundrum.

So I have this table in excel:

I want to sum the value of each cell opposite to "a", but only if it is not followed by a cell "b"  (in bold and underlined).

In this example I should get 500.

Or vice versa, only sum the cells opposite to "a", which are followed by a "b".

Is there any (more or less simple) way it could be done?

Thank you very much in advance!

If your data is in range A1:B18...

"a" not followed by "b"

=SUMIFS(B2:B18,A2:A18,"a",OFFSET(A2:A18,1,0),"<>b")

"a" followed by "b"

=SUMIFS(B2:B18,A2:A18,"a",OFFSET(A2:A18,1,0),"b")

Hi psmir,

I agree with Mark18991230, his suggestion is worked.

On the other hand, we also can add a help column to get your goal. See this:

C1: =IF(OR(A2="b",A2=""),"",IF(AND(A2="a",A3="b"),1,2))

E3: =SUMIF(C:C,1,B:B)

E4: =SUMIF(C:C,2,B:B)

Hope it's helpful.

Regards,