or?

[MikeC2006]

instead of doing something like this:

=SUMIFS(W24:W9999,Z24:Z9999,">0",M24:M9999,">0")+SUMIFS(W24:W9999,Z24:Z9999,">0",N24:N9999,">0")-SUMIFS(W24:W9999,M24:M9999,">0",N24:N9999,">0",Z24:Z9999,">0")

is there a way to insert an "or" so the funtcion would look something like:
=sumifs(W24:W9999,Z24:Z9999,">0",M24:M9999,">0" OR N24:N9999,">0")

i know there is... i just can't find it... and searching for "or" on the message board or online help returns too many hits...

thanks for the help!

 

[Andrew Poulsom]

Use SUMPRODUCT:

http://www.eggheadcafe.com/software/aspnet/30597761/sumifs-and-or.aspx

 

[barry houdini]

Hello Mike, try using SUMPRODUCT like this

=SUMPRODUCT(W24:W9999,(Z24:Z9999>0)*((M24:M9999>0)+(N24:N9999>0)>0))

 

[Jonmo1]

=SUMIFS(W24:W9999,Z24:Z9999,">0",M24:M9999,">0")+SUMIFS(W24:W9999,Z24:Z9999,">0",N24:N9999,">0")-SUMIFS(W24:W9999,M24:M9999,">0",N24:N9999,">0",Z24:Z9999,">0")

I hope that is just an example that you just threw together, because it makes almost no sense...
It's checking the same criteria in
Cols Z M , then Z again, and N.
so it's adding Z twice, then M and N
Then it's subtracting Z M and N
Leaving one Z

so that can all be reduced to a single sumif, to check criteria in Z and sum W.
=SUMIF(Z24:Z9999,">0",W24:W9999)

 

[MikeC2006]

or something like this ?

=SUMIFS(W24:W9999,Z24:Z9999,">0",((M24:M9999 OR N24:N9999),">0"))

 

[MikeC2006]

Use SUMPRODUCT:

not sure how to use sumproduct... and the help file is cryptic... any help here?

 

[barry houdini]

See my earlier reply......

 

[MikeC2006]

I hope that is just an example that you just threw together, because it makes almost no sense...
It's checking the same criteria in
Cols Z M , then Z again, and N.
so it's adding Z twice, then M and N
Then it's subtracting Z M and N
Leaving one Z

so that can all be reduced to a single sumif, to check criteria in Z and sum W.
=SUMIF(W24:W9999,Z24:Z9999,">0")

Jonmo, it works for what I need it to do, it's just not very elegant

I don't think you're example (sumif) will work because i need it to check M or N...

I need to sum W if z>0 and (M or N)>0

=SUMIFS(W24:W9999,Z24:Z9999,">0",((M24:M9999 OR N24:N9999),">0"))

 

[MikeC2006]

=SUMIFS(W24:W9999,Z24:Z9999,">0",M24:M9999,">0")+SUMIFS(W24:W9999,Z24:Z9999,">0",N24:N9999,">0")-SUMIFS(W24:W9999,M24:M9999,">0",N24:N9999,">0",Z24:Z9999,">0")

I hope that is just an example that you just threw together, because it makes almost no sense...
It's checking the same criteria in
Cols Z M , then Z again, and N.
so it's adding Z twice, then M and N
Then it's subtracting Z M and N
Leaving one Z

so that can all be reduced to a single sumif, to check criteria in Z and sum W.
=SUMIF(Z24:Z9999,">0",W24:W9999)

it sums W if Z>0 and M>0 then sums W if Z>0 and N>0 then subtracts W when Z>0 and M>0 and N>0 giving me the sum of W when Z>0 and M or N>0

(if i didn't add the subtraction at the end, my result was twice as large as it should have been because if there were values in M and N it added it twice)

 

[Jonmo1]

OK, got it now...It was hard to wrap my head around the original formula...LOL...

Barry's should do what you want.