SUMIFS and OR

[mohavv]

Hi,

Is there a way to use the OR function within the SUMIFS function, if
you only want to sum two criteria-values (not consecutive).

Cheers,

Harold

 

[Jim Thomlinson]

SumProduct perhaps???

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
It looks like this web page might be temporarily down. Not too sure...

 

[Herbert Seidenberg]

Example:
AA BB CC DD EE
45 E 101 E 3
71 B 105 D 6
64 B 102 C 3
42 E 103 C 2
50 B 104 D 7
36 D 101 E 1
84 D 102 B 3
86 B 105 D 8
18 C 102 D 7
27 D 105 C 5
46 E 105 B 9
42 C 101 D 9
Regular ANDed SUMIFS and SUMPRODUCT formulas might be
=SUMIFS(AA,BB,"B",CC,105,DD,"D",EE,">5") =157
=SUMPRODUCT(AA*(BB="B")*(CC=105)*(DD="D")*(EE>5)) =157
The same formulas with ORed criteria would be
=SUM(AA)-SUMIFS(AA,BB,"<>B",CC,"<>105",DD,"<>D",EE,"<=5") =404
=SUMPRODUCT(AA*NOT(NOT(BB="B")*NOT(CC=105)*NOT(DD="D")*NOT(EE>5)))
=404

 

[mohavv]

Example:
AA      BB      CC      DD      EE
45      E       101     E       3
71      B       105     D       6
64      B       102     C       3
42      E       103     C       2
50      B       104     D       7
36      D       101     E       1
84      D       102     B       3
86      B       105     D       8
18      C       102     D       7
27      D       105     C       5
46      E       105     B       9
42      C       101     D       9
Regular ANDed SUMIFS and SUMPRODUCT formulas might be
=SUMIFS(AA,BB,"B",CC,105,DD,"D",EE,">5")    =157
=SUMPRODUCT(AA*(BB="B")*(CC=105)*(DD="D")*(EE>5))  =157
The same formulas with ORed criteria would be
=SUM(AA)-SUMIFS(AA,BB,"<>B",CC,"<>105",DD,"<>D",EE,"<=5")   =404
=SUMPRODUCT(AA*NOT(NOT(BB="B")*NOT(CC=105)*NOT(DD="D")*NOT(EE>5)))
=404

I meant Two values in the same column/area.
example (not working) =SUMIFS(aa,bb,"B",bb,"E")
=SUMIFS(aa,bb,OR("B","E"))

Cheers,

Harold

 

[Herbert Seidenberg]

The same format applies.
Just OR the same column two (or more) times.
=SUM(AA)-SUMIFS(AA,BB,"<>B",BB,"<>E") =404
=SUMPRODUCT(AA*NOT(NOT(BB="B")*NOT(BB="E"))) =404