Countif question

[Dingy101]

Several people have been helpful getting me to point I am now at with this
spreadsheet.

Using formula below, is there a way to ignore data to right of decimal point?

I need to retain data to right in sorted answer.

Example at bottom is what I am trying to do now.


Data in the range A2:B17
In cell C2, key in
=IF(COUNTIF(A:A,B2)>COUNTIF(B$2:B2,B2)-1,B2,"")


A---------------------------B------------------------C
0.203 ------ 0.203 ------ 270.413
280.213 ------ 270.213 ------ 270.403
280.413 ------ 270.413 ------ 265.113
280.403 ------ 270.403 ------ 262.313
260.201 ------ 255.201 ------ 262.301
0.211 ------ 0.211 ------ 260.111
0.411 ------ 0.411 ------ 0.411
0.401 ------ 0.401 ------ 0.401
265.101 ------ 268.101 ------ 0.211
265.111 ------ 260.111 ------ 0.203
262.311 ------ 258.311 ------
262.301 ------ 258.301 ------
275.103 ------ 268.103 ------
272.113 ------ 265.113 ------
270.313 ------ 262.313 ------
270.303 ------ 262.303 ------


Thank You,

Gary

 

[Tom Hutchins]

Try this:

=IF(SUMPRODUCT(--(TRUNC($A$2:$A$17)=TRUNC(B2)))>(SUMPRODUCT(--(TRUNC($B$2:$B2)=TRUNC(B2)))-1),B2,"")

or this:

=IF(SUM(--(TRUNC($A$2:$A$17)=TRUNC(B2)))>SUM(--(TRUNC($B$2:$B2)=TRUNC(B2)))-1,B2,"")

The second is an array formula which must be entered by pressing
CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will
put curly brackets around the formula {}. You can't type these yourself. If
you edit the formula you must enter it again with CTRL+Shift+Enter.

Hope this helps,

Hutch

 

[Dingy101]

THANK YOU !!

First one works.

Gary

Try this:

=IF(SUMPRODUCT(--(TRUNC($A$2:$A$17)=TRUNC(B2)))>(SUMPRODUCT(--(TRUNC($B$2:$B2)=TRUNC(B2)))-1),B2,"")

or this:

=IF(SUM(--(TRUNC($A$2:$A$17)=TRUNC(B2)))>SUM(--(TRUNC($B$2:$B2)=TRUNC(B2)))-1,B2,"")

The second is an array formula which must be entered by pressing
CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will
put curly brackets around the formula {}. You can't type these yourself. If
you edit the formula you must enter it again with CTRL+Shift+Enter.

Hope this helps,

Hutch

 

[Dingy101]

I hate to have to be spoonfed this stuff, but I am in over my head with this.

Your answer worked fine for what I asked, which is to give me a third column
based on matches in first two.

What do I change to get a fourth column that subtracts what is in third
column from first column?

I was able to do this with the countif statement by changing the = to a <=
but I can't get that to work here.

Gary

 

[Tom Hutchins]

Although I wrote formulas (both give me exactly the same results) that did
what you asked, I don't understand what you are trying to accomplish. When
you say you want to subtract what is in column C from column A, that sounds
like =A2-C2 but I don't think that's what you mean. Can you please clarify?

Hutch

 

[Dingy101]

What I am working on is a spreadsheet to calculate the replacement valve
shims in a motorcycle engine. There are 16 valves that use a shim from 200
thru 320. Based on the measured clearance in the engine, one of two
replacement shims can be used if the valve is out of spec. I have all of this
logic working in the spreadsheet.

I have generated list

The first column (A) is a list of any shim that is being removed due to that
valve being out of spec. (If shim is in spec it is shown as Zero in this
column.)

The 2nd column (B) is the list of first choice shims to replace the out of
spec shims. (If shim is not needed due to existing valve is in tolerance it
is shown as a Zero in this column)

The third column (C) is the match of any shim that is needed in engine (col
B) subtracted from what is in engine but not needed (col A)

Now I am looking to take the list that is generated in column C and subtract
that from column A. This is a list of shims that did not match the first
choice shim. New column D

I will then take the 2nd choice shim list and look for any matches in Column
D (remaining unused shims and generate a list of matches (same as was done
for column C) This is new column E

I will finally take column E and subtract it from column D. This is a final
list of unused shims from engine. This will be new column F

I have been able to do all this with countif functions. I added the three
decimal places via a concantate statment to keep track of where each shim
came from in engine. The countif now will not work due to each shim in engine
has unique code in three decimal places.

Basically what I am trying to do beyond what you provided me with is to
generate the column D. I am now trying to subtract one list (column C) from
another (column A).

The last to columns (E & F) are just repeats of earlier column functions
with modified column designations in formulas.

Final goal is to look at all data and provide list of new shims needed. That
is reason for needing the three decimal places. This is a longer story.


Gary

 

[Tom Hutchins]

This formula in D2 (and copied down) should list the shims in column A which
are not found in column C:

=IF(ISNUMBER(FIND(TRUNC(A2),$C$2:$C$17)),"",A2)

I have another suggestion for you. Why not insert a column next to each
column that has the decimal shim values and use the TRUNC function to get
just the non-decimal part of each value? Then you can use IF/COUNTIF
functions on the integer shim values, returning the decimal shim values each
time. You can hide the columns with the TRUNC functions when you are done, if
you don't want to see them. I think a solution using formulas you understand
well is easier to adapt & maintain. One note: if an IF(COUNTIF formula might
return a value of "", then the TRUNC function in the column next to it must
test for that possibility:
=IF(F2="","",TRUNC(F2))

Hutch

 

[Dingy101]

THANK YOU !!

1st formula enabled me to go through what I described above to final left
over shims (column F)

I understand what you are saying about staying in my comfort zone, but if
that was all I ever did I would still be limited to adding up columns. With
the help of people like you, I am able to expand my abilities and go into new
and ,for me, uncharted areas!

I'll be back for more.

Gary