COUNTIF problems

[dobit]

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.

 

[ACtrafficengr]

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

 

[dobit]

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.

 

[MintJulep]

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

 

[IRstuff]

Why can't you use the Auto Filter?

TTFN

 

[NormPeterson]

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

 

[NormPeterson]

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

 

[MortenA]

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

Best regards

Morten

 

[onlyadrafter]

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!