Count # of rows that meet a criteria

Hi there!

Excel Spreadsheet example can be downloaded here.

I need to have a single cell giving me the total number of rows that meet the following criteria:
1. Row does not contain an "X" in Columns B:F
2. Row does not contain a number that is <=0 in Columns B:F
3. Row contains at least one number that >0 in Columns B:F

In this example the answer would be 2 (Rows 4 and 8).

Making Column G use a formula for each row and then doing a sum at the bottom is not an option. It needs to be a single cell. Any ideas? Thanks very much for your help. I've been puzzling over this for a while now.

~kory

I couldn't figure out how to do this in a single cell without a macro (though someone else might know how). I'm curious as to why it has to be a single cell. There might be other out-of-the-box solutions that would work for you, such as using a different worksheet, or an entirely separate workbook, depending on what your constraints are.

I'd like to avoid a macro just for simplicity's sake, but will use one if necessary. This spreadsheet is just an example of a much larger worksheet (1523 rows and 28 columns) that is part of a series of reports I run regarding product placement for our company. There are already several worksheets in the workbook, so that's why I'd like to avoid adding another. Multiple people access and add information to this including inserting lines and I'd like to avoid the possibility of them forgetting to copy down the formula. That would result in constant upkeep on my part.

The other reason for using a single cell is that the cell that will be using this formula is actually part of a separate report on a different tab, though it's in the same workbook. It will just be linking to the tab that the example worksheet is on.

I can usually figure most of the formulas I need out, which means I only ever toss the hard ones out to the interwebs for help. =/

To re-word my problem which may spark ideas for someone: I need to count the number of times all 3 of the above criteria register "True".

Here's my out of the box solution (requires multiple cells)
I set up the table in I6:L13 but you could set it up on a different sheet or hide the columns.
Each column of table represents a criteria (3 criteria, 3 columns) and if the row meets that criteria, it imputs a 1.

Doesn't contain an X
=--(COUNTIF(B2:F2,"X")<1) dragged down

Doesn't have a number <= 0
=--(COUNTIF(B2:F2,"<=0")<1) dragged down

Has at least 1 number > 0
=--(COUNTIF(B2:F2,">0")>0) dragged down

Then using SUMPRODUCT to count times all 3 criteria are met (Cell I6)
=SUMPRODUCT(J7:J13,K7:K13,L7:L13)

Agreed with the economy of combining the 3 formulas to 1 cell. I tried coming up with an array formula that would work but could not.

Try:

Please Login or Register  to view this content.

NBVC, you sir are a genius. That worked beautifully.

Was hoping I'd be able to modify this formula slightly, but I'm having difficulty getting the next result needed. How would I modify this to just show how many rows contain a number greater than 1 and remove the other criteria?

The critieria "sections" start with "--" and end with a "," so take out those sections you no longer need.

For that try:

=SUMPRODUCT(--(MMULT(($B$2:$F$8>0)*(B2:F8<>"X"),{1;1;1;1;1})>0))