SUMIFS

[Steen]

Normally SUMIFS uses an AND operator for the multiple criteria. How can I use
an OR operator. Example. I would like to calculate the sum of specific range
in case criteria 1 or criteria 2 is TRUE.

 

[Sheeloo]

Sumif(conditonA)+sumif(conditionB)-sumproduct(conditionA,conditionB)

example
=SUMIF(A1:A10,"D",C1:C10)+SUMIF(B1:B10,"C",C1:C10)-SUMPRODUCT((--(A1:A10="D")),(--(B1:B10="C")),(C1:C10))

 

[T. Valko]

Well, it depends on the exact criteria.

x.....1
y.....5
c.....3
x.....2
k.....5

To sum column B where column A equals either x or y:

=SUM(SUMIF(A1:A5,{"x","y"},B1:B5))

 

[Ashish Mathur]

Hi,

You may also want to read up on the Database functions of Excel. To specify
OR conditions, the condition has to be written one below the other. D
functions are very well explained in Excel's Help menu

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[new1]

Hi,

You may also want to read up on the Database functions of Excel.  To specify
OR conditions, the condition has to be written one below the other.  D
functions are very well explained in Excel's Help menu

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

Hello,

In this case, i would rather create an indicator column stating if
yes or no, one of the two conditions are verified.
If yes output 1 else output 0 in the column.
And then include this indicator column in a sumproduct formula like
this

= SUMPRODUCT (value column, indicator column).

Hope that helps.

Regards

Fabien.

 

[ShaneDevenshire]

Hi,

Yes, the same way as in 2003, except in 2007 you can use SUMIFS or SUMIF:

=SUMPRODUCT(SUMIFS(B1:B8,A1:A8,E1:E2))

where the two or criteria are in E1:E2. Or you can array enter the formula

=SUM(SUMIFS(B1:B8,A1:A8,E1:E2))

In both these examples the sumrange is B1:B8, the criteria range is A1:A8.

You can extend this idea in 2007 in a way you could not (using SUMIF in
2003), you can do an AND-OR criteria. In the following example D1:E2 look
like this

4 B
8 A

=SUMPRODUCT(SUMIFS(B1:B8,A1:A8,E1:E2,B1:B8,D12))