SUMIFS
 

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.

SUMIFS
 

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

"Steen" wrote:

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.

SUMIFS
 

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

--
Biff
Microsoft Excel MVP


"Steen" wrote in message
...
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.

SUMIFS
 

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

"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in
message ...
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))

"Steen" wrote:

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.

SUMIFS
 

On 1 nov, 06:50, "Ashish Mathur" wrote:
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

"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in
...

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

"Steen" wrote:

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.

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.

SUMIFS
 

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,D1:D2))


--
Thanks,
Shane Devenshire


"Steen" wrote:

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.