6

Using Google Sheets with the expression SUMIF, is there a way to make the criteria field look for 2 types of values using OR logic?

A    B  
Joe  1  
Joe  2  
Tim  3  
Roy  4  
Tim  5  
Roy  6  

How do I make a SUMIF for the rows that have Joe and Roy so the result is 13?

Someone showed me how to do it on LibreOffice Calc simply with =SUMIF(A1:A6;"=Joe|Roy";B1:B6), but that doesn't seem to work on Sheets. I've currently resorted to a query like so, =sum(query(A:B,"Select B where A = 'Joe' or A = 'Roy'")), is there a simpler way with just incorporating the pipe |?

6

Use of REGEXMATCH

There are many ways to do this in Google sheets. One is:

=SUM(filter(B:B,REGEXMATCH(A:A,"Joe|Roy")))

to make it with sumif:

=SUMIF(ARRAYFORMULA(REGEXMATCH(A:A,"Joe|Roy")),true,B:B)

Filter with OR logic

and this formula is ok:

=sum(FILTER(B:B,(A:A="Joe")+(A:A="Roy")))

but it's not so easy to use when you want it matching more then 2 names.


DSUM function

To sum whith multiple criteria, use DSUM.

Prepare data A1:B7:

Name    Sum
Joe       1
Joe       2
Tim       3
Roy       4
Tim       5
Roy       6

Prepare range with conditions D1:D3:

Name
Joe
Roy

And use formula DSUM:

=DSUM(A1:B7,B1,D1:D3)
| improve this answer | |
  • well that did the trick, thanks why's regexmatch necessary in this scenario btw? – timoseewho Apr 20 '16 at 6:27
  • 1
    As said in help, criterion can contain wildcards including ? to match any single character or * to match zero or more contiguous characters, but nothing said about "|" char to match two or more values. – Max Makhrov Apr 20 '16 at 6:57
1

This can be accomplished using SUMPRODUCT and REGEXMATCH.

=SUMPRODUCT(REGEXMATCH(A:A,"Joe|Roy"), B:B)

enter image description here

It works by first applying REGEXMATCH to every entry in A:A. This results in an array of TRUE (1) or FALSE (0) values as shown in column C and D. Then SUMPRODUCT is called which multiplies each 1 or 0 by the values in B:B as shown in the formula:

1*1 + 1*2 + 0*3 + 1*4 + 0*5 + 1*6 = 13
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.