OR function in SUMIFS

**skysurfer** · 05-09-2012, 12:00 AM

Hi,

Is there a way to include the OR function--or functionality--in SUMIFS criteria? I am looking for a range to satisfy three criteria: = A OR B OR C.

I was able to build that functionality into SUMPRODUCT (thanks to excelforum!), but my goal is to optimize performance and replace all the SUMPRODUCT functions.

Thanks,

Lawrence

**shg** · 05-09-2012, 12:11 AM

=sum(countif(a1:a20, {"a","b","c"}))

**skysurfer** · 05-09-2012, 12:34 AM

shg,

How will COUNTIF will generate the sum? Assuming you meant SUMIF, can I use cell references for A, B and C. And since there are multiple conditions can I use SUMIFS?

Thanks!

Lawrence

**skysurfer** · 05-09-2012, 12:46 AM

shg,

Okay, making progress!

Got your formula to work using constants A, B and C SUM(SUMIFS) function combo. But how do I use cell references instead of constants???

Lawrence

**shg** · 05-09-2012, 12:55 AM

For a contiguous range,

=SUM(COUNTIF(A1:A20, B1:D1))

Otherwise,

=SUM(COUNTIF(A1:A20, CHOOSE({1,2,3}, B1, C2, D3)))

Either must be confirmed with Ctrl+Shift+Enter.

**skysurfer** · 05-09-2012, 01:27 AM

shg,

Having a difficult understanding.

I have the following working:
=SUM(SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,{"A","B","C"}))

But when I replace A, B, and C with cell references, the formula fails. Is the only way to make it work to convert it into a SHFT-CTRL-ENTER array? If so, is the best I can do performance-wise is to use SUMPRODUCT?

Anyway, really appreciate your help. How do I make the cell links work...or do you recommend SUMPRODUCT?

Lawrence

**JosephP** · 05-09-2012, 02:41 AM

you cannot use cell references as an array constant. see shg's use of choose in the last post. if still stuck I'd suggest you provide a specific example. I reckon you'd probably do as well with sumproduct or a pivot.