COUNT WITH IF 1

[davidneedshelp]

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.

 

[witchdoc]

insert the following formula in column D
=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.

 

[davidneedshelp]

Thanks for that witchdoc. This works, but my example was simplified:

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.

 

[ACtrafficengr]

Have you tried the COUNTIF function?

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

 

[davidneedshelp]

Yes i have, but without success.

 

[joerd]

Try this one:
[tt]=SUM(IF(($B1:$B5=2006)*(RIGHT($A1:$A5,1)="F"),C1:C5,0))[/tt],
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.

 

[davidneedshelp]

Thanks Joerd - problem solved.

 

[yogia]

Hi David:

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

 

[davidneedshelp]

Thanks Yogi.