COUNTIF() Woes: OR Logic

  • Thread starter Thread starter Fao, Sean
  • Start date Start date
F

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,
 
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,
Click to expand...

= {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)
 
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
 
recrit said:
= {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)
Click to expand...

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!
 
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
 
Bernie said:
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.
Click to expand...


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!
 
Back
Top