In article <[log in to unmask]>, Larry Boyd
<[log in to unmask]> writes
>I have an MS Excel question.
>
>I have two columns per row. If C1 equals "1" and C2 equals "2", I would
>like to count it. I have tried SUMIF, SUMPRODUCT, COUNTIF, COUNT, IF, and
>AND. The problem is I can't figure out how to count a row based on two
>columns.
>
>Any ideas?
>
>TIA
>
>LB
If I understand correctly:
You need two steps, the first of which constructs a 'boolean' for your
logical condition, and which will need a column of its own:
working in row 1 of the spreadsheet, where A and B are your columns, put
the following formula in cell C1
=IF(A1="1",IF(B1="2",1,0),0)
then if and only if A1 = '1 ( you need to type in '1, not "1", nor 1)
and B1 = '2 (ditto)
then C1 will be 1.
I don't know if your "1" is meant to represent numeric 1, or the text
representation of 1. 'IF' seems picky, as above, and this could be
confusing.
Replicate this rule for 'nnn' cells down column C (as far as you need).
Then step 2:
You can put a SUM (C1:Cnnn) below these in column C, and that will tell
you how many rows meet your criteria.
Having got, in effect, a Yes/No flag in column C, you could then use
COUNT as you originally intended. And indeed, for more sophisticated
processing, you could use pretty much anything that COUNT will allow as
the contents of C....
OR:
Terser, sexier, but more opaque and less versatile would be to use:
=AND(A1="1",B1="2") in the 'C' column beside your values
=COUNTIF(C1:Cnnn,TRUE) in the 'C' column below your values
as above.....
HTH
--
Roy Brown Phone : (01684) 291710 Fax : (01684) 291712
Affirm Ltd Email : [log in to unmask]
The Great Barn, Mill St 'Have nothing on your systems that you do not
TEWKESBURY GL20 5SB (UK) know to be useful, or believe to be beautiful.'
|