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
------------------------------------------
"...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
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
RE: COUNTIF problems
TTFN
RE: COUNTIF problems
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
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
Its a very powerfull tool - and if you really want to impress everybody then a pivot table will usually do it
Best regards
Morten
RE: COUNTIF problems
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!