×
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

COUNTIF problems

COUNTIF problems

COUNTIF problems

(OP)
I have a spreadsheet that has about 20 columns and 500 rows.  I need to count how many times a reactor number appears in a particular column and what type of raw materials and temperature (shown on different columns)were used. Atimes, we may use a reactor for different runs by varying the ingredents, temp and etc.

RE: COUNTIF problems

Sounds like Countif will work. What problems are you having with it?

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

RE: COUNTIF problems

(OP)
ACtrafficengr,

For example, column B may contain reactor numbers 1 thru' 20 while column C may contain ingredients A to Z snd column D may contain percentage yields.

I need to be able to pick a reactor from column B then know how many times it was used for ingredient 'A' and what the yields are in column D.

RE: COUNTIF problems

Your question is unclear.  However it is clear that you should be using a proper database, not excel.

RE: COUNTIF problems

Why can't you use the Auto Filter?

TTFN



RE: COUNTIF problems

Try this (I'm keeping my fingers crossed that the formatting doesn't get trashed by HTML).

Write a string formula that combines columns B & C into Column E (=D10&E10).  At this point, you can do a countif on column E, where your criteria is also a string formula that’s comprised of the reactor and ingredient combination that you’re searching under.  Row F contains if tests and returns the D column Yield value if the E-column value matches the COUNTIF criteria and is blank if the E column value does not match.

Column        B                            C
Row 2        Reactor                    8
Row 3        Ingredient                b
Row 4        COUNTIF criteria    =C2&C3
Row 5        COUNTIF results    =COUNTIF(E9:E17,C4)

Column        B                C                D                E                F
Row 8        Reactor     Ingredient    Yield            R&I        Yield for search criteria
Row 9        (value)        (value)        (value)        =B9&C9    =IF(E9=$C$4,D9,"")


Norm

RE: COUNTIF problems

Edit to the above:  the formula that reads   =D10&E10 in the explanatory text should read =B9&C9.  The formatting survived but I don't fancy going through it again.

The formulas were written based on a very small table, only 9 rows deep.  But it seems to do what I think you're attempting, and doesn't choke if there are no matches.

Norm

RE: COUNTIF problems

To me this sound like a typical pivot table job!

Its a very powerfull tool - and if you really want to impress everybody then a pivot table will usually do it smile

Best regards

Morten

RE: COUNTIF problems

How about the following, you will need to amend columns to suit.

In Column E enter a formula =B1&C1.

In G1 enter the data you wish to find e.g. 2B

In Column I enter 1, 2, 3 down as far as required.

in Column J enter the following formula

=INDEX($D$1:$D$5,SMALL(IF($E$1:$E$5=$G$1,ROW($B$1:$B$5)),I1))

when entering the formula use CTRL, SHIFT and ENTER together.

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

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