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

SumProduct perhaps???

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

Jim Thomlinson


"mohavv" wrote:

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

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")*(EE5)) =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(EE5)))
=404

On Jan 30, 2:21*pm, Herbert Seidenberg
wrote:
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")*(EE5)) *=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(EE5)))
=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

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