Geek asks and answers

Amount values ​​if any cell in the corresponding row matches the text

I have the following Google Spreadsheet 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 any name appears in any of the three columns of the name. If so, then sum all the values ​​in the Value column for all matching rows.

For example, I want to summarize all the values ​​for the name "A". This name appears only in the first two lines, so I need to do 20 + 30 to give me 50. If I change the name to "C", it will appear in all three lines, so it should sum all the numbers: 20+ 30 + 40. The algorithm must correctly configure and perform the search.

I could not understand this, so I hope for help.

+5
source share
1 answer

= DSum will work

In the example you are using,

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

You can swap "A" for cell reference

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

+3
source

More articles:


All Articles