×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

COUNT WITH IF

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.















































RE: COUNT WITH IF

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.

RE: COUNT WITH IF

(OP)
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.

RE: COUNT WITH IF

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

RE: COUNT WITH IF

(OP)
Yes i have, but without success.

RE: COUNT WITH IF

Try this one:
=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

(OP)
Thanks Joerd - problem solved.

RE: COUNT WITH IF

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

RE: COUNT WITH IF

(OP)
Thanks Yogi.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources