Counting rows based on the contents of multiple cells
 

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

Counting rows based on the contents of multiple cells
 

Bret wrote:
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/xlFunctio...tml#SumProduct

Counting rows based on the contents of multiple cells
 

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<""))

--
Biff
Microsoft Excel MVP


"Bret" wrote in message
...
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