# Adding and/or logic into the criteria field (SUMIF)?

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 |?

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)
``````
• well that did the trick, thanks why's regexmatch necessary in this scenario btw? – timoseewho Apr 20 '16 at 6:27
• 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

This can be accomplished using SUMPRODUCT and REGEXMATCH.

``````=SUMPRODUCT(REGEXMATCH(A:A,"Joe|Roy"), B:B)
`````` 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
``````