CODEWITHSUNDEEP
excel
user3487243
user3487243

Reputation: 193

Dynamically sum cells whose neighboring cell contains certain text

In my spreadsheet, I am tallying expenses made by John, Bob, and Simon for a project.

When John records an expense, it must contain his name (not case sensitive):

A                B

Apples(John)     50

Simon makes 90% of the expenses and doesn't always write his name down.

At the end of the project, an expense list might look like this:

  A                 B

  Apples(John)      50
  Toy (bob)         15
  Pencils John      30
  Metal             10
  Peaches (simon)   5  
  Donuts BOB        30

Without using macro's (which don't work on Android's Excel app), how can I dynamically sum their expenses as each new entry is entered?(I am logging expenses on the desktop and on the phone, synced through the cloud). I would like one cell for each team members total. This is tricky for me because I have to sum items in B if its corresponding entry in A contains a certain text. Something like:

Totals:

John         80
Simon        15
Bob          45

Upvotes: 0

Views: 514

Answers (1)

Harun24hr
Harun24hr

Reputation: 37135

SUMIF() function will meet your requirement. You have to use wildcard in criteria part as names are mixed with item name. Try below formula.

=SUMIF($A$1:$A$6,"*" & D1 & "*",$B$1:$B$6)

enter image description here

Upvotes: 2

Related Questions