COUNTIF() Woes: OR Logic

[Fao, Sean]

I'm attempting to write a formula using the COUNTIF() function with OR
logic. Essentially, I would like to count the row if *either* of the
two conditions below are met:

=COUNTIF(V:V, "> 7")
=COUNTIF(G13:G2000, "<" &TODAY() - 14)

And actually, to make it even more difficult, the second formula
requires that an AND condition be met (though I think I can figure this
out if I can figure out the first problem).

I've attempted all sorts of variations of using the AND() and OR()
functions to produce the desired results; however, it appears as though
this functionality is not supported in Excel.

Is there any way to produce the desired results?

Thank you in advance,

 

[Pete_UK]

Can't you just add the results of the two formulae together?

Hope this helps.

Pete

 

[recrit]

I'm attempting to write a formula using the COUNTIF() function with OR
logic.  Essentially, I would like to count the row if *either* of the
two conditions below are met:

=COUNTIF(V:V, "> 7")
=COUNTIF(G13:G2000, "<" &TODAY() - 14)

And actually, to make it even more difficult, the second formula
requires that an AND condition be met (though I think I can figure this
out if I can figure out the first problem).

I've attempted all sorts of variations of using the AND() and OR()
functions to produce the desired results; however, it appears as though
this functionality is not supported in Excel.

Is there any way to produce the desired results?

Thank you in advance,

= {SUM( If(V:V>7, 1, 0) + ( if(G13:G2000<Today()-14, 1, 0)*if([2nd
condition], 1, 0) ) )}
{} --> means enter as array formula, ctrl-shift-enter

=COUNTIF(V:V, "> 7")
=COUNTIF(G13:G2000, "<" &TODAY() - 14)

 

[Bernie Deitrick]

Sean,

This is AND:

=SUMPRODUCT((V13:V2000>7)*(G13:G2000<(TODAY()-14)*(G13:G2000>0)))

This is OR:

=SUMPRODUCT((((V13:V2000>7)+((G13:G2000<(TODAY() -
14))*(G13:G2000>0)))>0)*1)


If G13:G2000 cannot be blank you can leave out the
G13:G2000>0
parts.

HTH,
Bernie
MS Excel MVP

 

[Fao, Sean]

= {SUM( If(V:V>7, 1, 0) + ( if(G13:G2000<Today()-14, 1, 0)*if([2nd
condition], 1, 0) ) )}
{} --> means enter as array formula, ctrl-shift-enter

=COUNTIF(V:V, "> 7")
=COUNTIF(G13:G2000, "<" &TODAY() - 14)

I see where you were going with this, but I just can't figure it out
from there.

In pseudo, this is what I want it to do.

COUNTIF(rows V:V > 7 OR ((G13:G2000 < TODAY() - 14) AND H13:H2000 =
"(blank)"))

In words, it's counting the row if any values from the V column are
greater than 7, *or* anything from G13:G2000 is older than 14 days ago,
but only if the value from the H column for the row is = "(blank)".

[condition] OR ([condition] AND [condition])

Hopefully that makes sense.

Any help on this is greatly appreciated. I've been struggling with this
for days!

 

[Pete_UK]

You've now added your AND condition, but I was thinking along the
lines of simply:

=COUNTIF( ... ) + COUNTIF( ... )

for the examples you first quoted.

COUNTIF and SUMIF can only be used for one condition, but you can use
SUMPRODUCT to mimic both these functions in situations where you have
two or more conditions. Bernie has given you examples of how you can
achieve your requirements (and he's an MVP !!)

Pete

 

[Fao, Sean]

Sean,

This is AND:

=SUMPRODUCT((V13:V2000>7)*(G13:G2000<(TODAY()-14)*(G13:G2000>0)))

This is OR:

=SUMPRODUCT((((V13:V2000>7)+((G13:G2000<(TODAY() -
14))*(G13:G2000>0)))>0)*1)


If G13:G2000 cannot be blank you can leave out the
G13:G2000>0
parts.

Ok, so I *think* I've got it now! I had to do a little bit of reading
to understand what you were saying, but I think it's working.

Thank you very much for your help!