CODEWITHSUNDEEP
google-sheets
void.pointer
void.pointer

Reputation: 26355

Sum values if any cell in corresponding row matches text

I have the following Google Sheets data:

Name1   Name2   Name3    Value
A       B       C        20
B       A       C        30
C       D       F        40

What I'm trying to do is see if a specific name appears in any of the three name columns. If it does, then sum all the values in the "Value" column for all matching rows.

For example, I want to sum all of the values for name "A". That name only appears on the first two rows, so it should do 20+30 to give me 50. If I change the searched name to "C", that appears in all three rows so it should sum all of the numbers: 20+30+40. The algorithm needs to adjust and search appropriately.

Upvotes: 5

Views: 4781

Answers (1)

eddyparkinson
eddyparkinson

Reputation: 3700

=DSum will work

With the example you give use

=dsum(A1:D4,D1,{A1;"A"})+dsum(A1:D4,D1,{B1;"A"})+dsum(A1:D4,D1,{C1;"A"})

You can swap the "A" for a Cell reference

see https://drive.google.com/previewtemplate?id=0As3tAuweYU9QdEVHdTFHNzloSTY4LVYxdW9LdHRHbEE&mode=public#

Upvotes: 2

Related Questions