COUNT WITH IF
COUNT WITH IF
(OP)
Suppose I have 3 columns of data. If the value in the first column contains "F" and the value in the second column is "2006" then I want to add the number in the third column to a running total of the numbers in the thrid column that satisfied the criteria.
For example:
LF 2006 1 (True)
LR 2006 1 (False)
RF 2006 0 (True)
LF 2007 1 (False)
RF 2006 1 (True)
This should return 2. (1+0+1)
Thank you for your help.
For example:
LF 2006 1 (True)
LR 2006 1 (False)
RF 2006 0 (True)
LF 2007 1 (False)
RF 2006 1 (True)
This should return 2. (1+0+1)
Thank you for your help.





RE: COUNT WITH IF
=IF(AND(B1=2006,RIGHT(A1,1)="F"),C1,0) and copy to the bottom of your range. Sum this column and you get your answer.
RE: COUNT WITH IF
LF 2006 1 0 0
LR 2006 1 0 1
RF 2006 0 1 1
LF 2007 1 0 1
RF 2006 1 0 1
I have around 20 columns of data in the form of 1's and 0's and I wish to total each column according to the criteria. So here the total of column C should be 2, D 1 and E 2.
Your method would add an extra column of data for each existing column. Is there a way of achieving this with one line of code? I was trying COUNT with a nested IF.
Thank you for your help.
RE: COUNT WITH IF
"...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928
"I'm searching for the questions, so my answers will make sense." - Stephen Brust
RE: COUNT WITH IF
RE: COUNT WITH IF
=SUM(IF(($B1:$B5=2006)*(RIGHT($A1:$A5,1)="F"),C1:C5,0)),
to be entered as an array formula (instead of <Enter> press Ctrl-Shift-Enter).
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: COUNT WITH IF
RE: COUNT WITH IF
Based on your statement I have considered column A entries to contain F (and not limit it to be just the right most character). If your data posted is in cells A1:E5, then in a cell of column C, say cell C7, key in the following formula ...
=SUMPRODUCT(ISNUMBER(FIND("F",$A$1:$A$5,1))*($B$1:$B$5=2006)*(C$1:C$5=1))
then copy it to cells D7 through E7.
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com
RE: COUNT WITH IF