Re: CountIf with VBA by shiro
shiro
Fri May 09 07:39:13 CDT 2008
Mr Ron,
this formula
=SUMPRODUCT(--(G:N=G16),--(G:N=K16),--(G:N=L16),--(G:N=M16),--(G:N=N16))
return #NUM! for me...
"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:n6g824pedcoc27ljbi78qfuv3hvqr5ht5a@4ax.com...
> On Fri, 9 May 2008 15:34:55 +0800, "shiro" <shiro@play.com> wrote:
>
> >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