Sum after Sorting

[Peter]

There is an Excel Spreadsheet like the following

Project Value Sum
Power 120 354 (Power + Computer)
Computer 234
AV 123
Quest 200

In other words, the formula for 354 is "=B2+B3"

There is a requirement that we have to sort the first
column in ascending order but keep the sum still be the
value for Power & Computer (as follow)

Project Value Sum
AV 123 354
Computer 234
Power 120
Quest 200

I have tried a number of ways but still cannot get it
fixed. Is there any suggestion ?

 

[Biff]

Hi!

Based on your example in the second table it looks like
you sorted both columns A and B but used column A as the
key.

=SUMPRODUCT((A2:A5={"power","computer"})*B2:B5)

Biff

 

[Peter]

Dear Biff,

Thank you for your advice. It works properly.

On the other hand, if the content in the column A contains
not only 1 word - Like "Computer System on the 1/F in the
headquarter", "Power should be including the UPS and
backup system" .... etc. Is it necessary to contain all
wordings or can I use LIKE {"Power*", "Computer*"} ?

Thanks

 

[Tom Ogilvy]

=sumif(A:A,"Power*",B:B)+sumif(A:A,"Computer*",B:B)

or

=SUM(SUMIF(A:A,{"Power*","Computer*"},B:B))