Rows and countif functions used together?

  • Thread starter Thread starter mhauler
  • Start date Start date
M

mhauler

Hi Everyone,

I'm trying to count the number of rows in a spreadsheet that meet two
conditions: For example, I want to count the number of rows in which
column b = "northeast" but also for which column a does NOT = "99"

I'm trying something along the lines of the following. It's obviously
not right, so I hesitate to even copy it here, but just in case I'm on
the right track, perhaps someone could give me an assist...

I've tried lots of different things before I got to this point and
sought outside help, so this might be way off track anyway...

=ROWS((COUNTIF (B2:B699,"Northeast")) AND (COUNTIF (A2:A699, <> "99"))


Thanks very much in advance...
 
Thanks Don,

I'm not too sure about the '*' since there's no multiplying going on
but at this point, I'm game to try almost anything....

Will let you know if this works.

Mar
 
=ROWS((COUNTIF (B2:B699,"Northeast")) AND (COUNTIF
(A2:A699, <> "99"))

The <> won't work. Try this.

=Abs( countif(b2:b699,"Northeast") - count(a2:a699)-
countif(a2:a699,"99") )

The 99 should only be in quotes if it is text.

To get the count of <> "99" what you basically do is count
all of then and subtract the ones that = "99". The ABS
function covers you for cases where there are more in A
<>"99" than B="Northeast". IT will work for the other case
as well.

Good luck.
 
Here's a simplified example...

For the NE records, there are three records I want to count and two
that have 99 that I don't want counted, so I'm trying to find a formula
that shows me just the number of records for the ne that don't have 99
in column a...

I appreciate everyone's help -

Mark


+----------------------------------------------------------------+
| Attachment filename: fnct_expl.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=377954|
+----------------------------------------------------------------+
 
The suggested SumProduct formula for multiconditional counting should work.
Here an extended version...

=SUMPRODUCT(--($A$3:$A$10<>99),--($A$3:$A$10<>""),--($B$3:$B$10="ne"))

which excludes empty cells in the A-range.
 
I tried it on my *real* spreadsheet (which actually uses "Northeast" and
it didn't work. I don't understand how that * symbol fits in since
there's no math going on - just counting, but I'll revisit it in the
morning when I get back to the office. I obviously could have
fat-fingered something. I'll try your suggestion again.

Thanks very much (again) for your suggestion. I certainly didn't want
you to think I didn't try it when you first offered our suggestion.

Mark
 
Thanks to both of you...I plugged the formulae in this morning and the
both worked just fine.

Don, I still don't understand usage of the asterisk, but the number
are certainly correct.

Thank you both very much...

Mark Hauler
Business Analyst
Ceridia
 
Back
Top