SUMIF with multiple criteria (maybe SUMIFS better)

Hi all,

Been having an issue with SUMIF or SUMIFS. Would appreciate any help on this. Have attached an excel as a reference.

I want a formula whereby i get all calues of the Letter A on the condition that the cell above this letter A has at least one word within a named range.

I have 2 names ranges:
list = B2:B22
test=A2:B2

In my sheet, I want to add the values in column C which have the value A in column B AND also that the cell above this contains either 'alpha' or 'beta' (will make more sense in the excel).

It is important to refer to the named ranges. So simply, if I wanted to add up the values of A, I would use:
=sumif(list,E1,offset(list,,1,,)) This gives value of 75

I want a similar formula to give a value of 65 (summation of cells highlighted in orange)

In essence I want a fromula like:

=sumif(list,there is a letter A such that the cell above it contains any of the words within my 'test' named range (alpha or beta),offset(list,,1,,)) This gives value of 65 as only cells C3,C6 and C16 have a letter A in column B AND that the cell above this has either the word alpha or beta.

Many thanks to all.

This should do it for you

Formula:

Please Login or Register  to view this content.

Amazing - Cheers Bob