Hi,Thank's for the response
I work with excel 2000.Let see my sample data lay out
Column : A B D E F G
DATA : OK C3S 81A.01 818 354 formula must return
2
NG C3S 81A.01 818 351 formula return 1
NG C3S 81A.01 818 354 formula return 1
OK C3S 81A.01 818 354 formula must
return 2
From that data we can see there are two excatly same
data on row 1 and row 4.And I want to be able to count
the number of excatly similar data on cell G.
The data type are :
A: Text
B: Text
D: Text
E: Number
F: Number
I have tried SUMPRODUCT but it dependanciesonly to the value at column F.
Pivot table work fine but this time I need to try another way first before
using
pivot table.
Rgds,
Shiro
Excel 2000 does not have the COUNTIFS function which would allow you to set
multiple criteria, so you need to use SUMPRODUCT.
If you are trying to match "entire lines", and if your data range is, for
example, rows 1:10, then one method of doing that, with sumproduct, is:
=SUMPRODUCT(--($A$1:$A$10=A1),--($B$1:$B$10=B1),--($C$1:$C$10=C1),--($D$1:$D
$10=D1),--($E$1:$E$10=E1))
Enter that in some cell and fill down ten rows.
You should also add a test to ensure there is data in the referenced cells on
the particular row. E.g.:
=IF(COUNTA(A1:E1)=5,SUMPRODUCT(--($A$1:$A$10=A1),
--($B$1:$B$10=B1),--($C$1:$C$10=C1),--($D$1:$D$10=D1),--($E$1:$E$10=E1))," ")
=IF(COUNTA(A1:E1)=5,SUMPRODUCT(--($A$1:$A$10=A1),--($B$1:$B$10=B1),
--($C$1:$C$10=C1),--($D$1:$D$10=D1),--($E$1:$E$10=E1)),"")
Don't forget to "double up" on the quote marks when you set the formula in VBA:
"=IF(COUNTA(A1:E1)=5,SUMPRODUCT(--($A$1:$A$10=A1),--($B$1:$B$10=B1),
--($C$1:$C$10=C1),--($D$1:$D$10=D1),--($E$1:$E$10=E1)),"""")"
or, if you are using the R1C1 reference style in VBA, something like:
"=IF(COUNTA(RC[-8]:RC[-4])=5,SUMPRODUCT(--(R1C1:R10C1=RC[-8]),--(R1C2:R10C2=
RC[-7]),
--(R1C3:R10C3=RC[-6]),--(R1C4:R10C4=RC[-5]),--(R1C5:R10C5=RC[-4])),"""")"
--ron
