counting help

M

Mike

I need some help please. I am using a worksheet that has these columns:
Order_NM, original_reason, Corrected_Reason, Reason, Item_ID, QTY, PRICE,
Order_date. I need to count the reason column only if the corrected_reason
column does not = reason correct. The issue is that there could be multiple
rows with the same Order_NM and I don't want to simply do a vlookup to count.
I already use this formula to count the order numbers so i am not duplicating
anything: =IF(A2=A3,0,1). I am looking for something similar to this. Any
help would be greatly appreciated.

Example of the xls worksheet:
Order_NM Corrected_Reason Reason Item_ID QTY PRICE Order_date
E0130573 REASON CORRECT REJECT CC NON-RESPON 135 4 1 6.59 12/1/2006

Thanks!
 
T

Toppers

Not clear whether you want counts for each ODER_NM but look at SUMPRODUCT
function.

=SUMPRODUCT(--(B1:B100<>"Reason Correct")) will count ALL "Corrected
Reasons" not equal to 'reason correct'

Or =COUNTIF(B1:B100,"<>Reason Correct")

HTH
 
M

Mike

ok, thats not exactly what i want. i will do the counting later. I need a 1,0
response from the formula. let me start by saying that column k2 is this
formula: =IF(A2=A3,0,1). That gets me an accurate number to count with so i
am not duplicating order_nm's. Now I have tried this formula a few ways:
=IF(OR(B2=C2,K2=0),0,1). This is really close to what I need but it doesn’t
quite get it. I need to count the reason column only if the corrected_reason
column does not = reason correct, thus giving me a 1 for false. the problem I
am having is the duplicate order_nm's. So I only need to count each order
number 1 time if any of the rows fall into this category. Does this make
sense?

Thanks,
Mike
 
T

Toppers

Try this in your 'helper' column:

=IF(AND(B2<>"Reason Correct",COUNTIF($A$2:A2,A2)=1),1,0)

and copy down

It should give you a 1 if "Corrected_Reason" <> 'Reason correct' (column
B)and only once for each order number(column A)

HTH
 
Top