Counting rows based on the contents of multiple cells

[Bret]

Hello,

We have a spreadsheet that is layed out something like this:


6 Collumns. Collumn A contains certain features of a software
product.
Collumn C contains X's, as checkmarks (indicating those features are
present). Collumn F contains comments regarding the features in
collumn A.


The table looks something like this:


A C F


feat1 X comment
feat2 comment
feat3 X


What we need to do is count the NUMBER OF ROWS that contain BOTH an X
in collumn C, and a comment in collumn F. I know how to count the
number of Xs and the number of Comments, but I need to be able to
count rows with BOTH. For intance, in my example, the formula would
equal 1, as only the first row has both a comment AND a checkmark.


I've been doing some reading and it seems like the dcounta formula is
what i'm looking for, but I cant seem to make it work.


Any suggestions would be greatly appreciated.


-Bret

 

[Glenn]

Hello,

We have a spreadsheet that is layed out something like this:


6 Collumns. Collumn A contains certain features of a software
product.
Collumn C contains X's, as checkmarks (indicating those features are
present). Collumn F contains comments regarding the features in
collumn A.


The table looks something like this:


A C F


feat1 X comment
feat2 comment
feat3 X


What we need to do is count the NUMBER OF ROWS that contain BOTH an X
in collumn C, and a comment in collumn F. I know how to count the
number of Xs and the number of Comments, but I need to be able to
count rows with BOTH. For intance, in my example, the formula would
equal 1, as only the first row has both a comment AND a checkmark.


I've been doing some reading and it seems like the dcounta formula is
what i'm looking for, but I cant seem to make it work.


Any suggestions would be greatly appreciated.


-Bret

See if this helps:

http://www.contextures.com/xlFunctions01.html#SumProduct

 

[T. Valko]

Try this:

count the NUMBER OF ROWS that contain
BOTH an X in collumn C, and a comment in
collumn F.

Assuming column F contains TEXT comments only, no numbers.

=SUMPRODUCT(--(C2:C100="X"),--(F2:F100<>""))