I have a spreadsheet which I need to return record counts based upon the row
containing any of 10 values in Column E. To make it worse, for a different
count, I will need to consder a subset of values in Column E as well. I
will need to do multiple variations of this tp establish a variety of metrics.

Yes, DB would be better, but not available to me.

I am just above the novice level. What VBA functions should I consider? Is
this a good use for an array? If so, some tips would be helpful. Or is
there a better (easier) approach?

Thanks for your help.

You might be able to do all you want by using a pivot Table report on your
data.


another approach

=SUMPRODUCT(--(A1:A10={1,3,5}))

counts rows that contain a 1, 3 or 5 in A1:A10.

--
Regards,
Tom Ogilvy



"Wiley" wrote:

I have a spreadsheet which I need to return record counts based upon the row
containing any of 10 values in Column E. To make it worse, for a different
count, I will need to consder a subset of values in Column E as well. I
will need to do multiple variations of this tp establish a variety of metrics.

Yes, DB would be better, but not available to me.

I am just above the novice level. What VBA functions should I consider? Is
this a good use for an array? If so, some tips would be helpful. Or is
there a better (easier) approach?

Thanks for your help.

Tom.

I tried that as shown in my code line here. I get a compile error of
"Expected Expression :)" with the ":" highlihghted after E2. intLastRw is
the last data record determined earlier in the code. Even if I use E2:E10, I
get the same error.

intClosedDRs =SUMPRODUCT(--(E2:("E" & intLastRow)={"AIE","AMS","AMB ","TMS"}))

Thanks.


"Tom Ogilvy" wrote:

You might be able to do all you want by using a pivot Table report on your
data.


another approach

=SUMPRODUCT(--(A1:A10={1,3,5}))

counts rows that contain a 1, 3 or 5 in A1:A10.

--
Regards,
Tom Ogilvy



"Wiley" wrote:

I have a spreadsheet which I need to return record counts based upon the row
containing any of 10 values in Column E. To make it worse, for a different
count, I will need to consder a subset of values in Column E as well. I
will need to do multiple variations of this tp establish a variety of metrics.

Yes, DB would be better, but not available to me.

I am just above the novice level. What VBA functions should I consider? Is
this a good use for an array? If so, some tips would be helpful. Or is
there a better (easier) approach?

Thanks for your help.

Sorry, that wasn't a VBA function. That was a worksheet Function. If you
want to use it in VBA, you would do

intClosedDRs = Application.Evaluate("SUMPRODUCT(--(E2:E" & _
intLastRow & ")={""AIE"",""AMS"",""AMB"",""TMS""}))")

checking the string in the immediate window:

intLastRow = 20
? ("SUMPRODUCT(--(E2:E" & _
intLastRow & ")={""AIE"",""AMS"",""AMB"",""TMS""}))")
SUMPRODUCT(--(E2:E20)={"AIE","AMS","AMB","TMS"}))

looks to produce a well formed formula

Note you had a space after AMB which I removed. If you want it, then put it
back.


--
Regards,
Tom Ogilvy



"Wiley" wrote:

Tom.

I tried that as shown in my code line here. I get a compile error of
"Expected Expression :)" with the ":" highlihghted after E2. intLastRw is
the last data record determined earlier in the code. Even if I use E2:E10, I
get the same error.

intClosedDRs =SUMPRODUCT(--(E2:("E" & intLastRow)={"AIE","AMS","AMB ","TMS"}))

Thanks.


"Tom Ogilvy" wrote:

You might be able to do all you want by using a pivot Table report on your
data.


another approach

=SUMPRODUCT(--(A1:A10={1,3,5}))

counts rows that contain a 1, 3 or 5 in A1:A10.

--
Regards,
Tom Ogilvy



"Wiley" wrote:

I have a spreadsheet which I need to return record counts based upon the row
containing any of 10 values in Column E. To make it worse, for a different
count, I will need to consder a subset of values in Column E as well. I
will need to do multiple variations of this tp establish a variety of metrics.

Yes, DB would be better, but not available to me.

I am just above the novice level. What VBA functions should I consider? Is
this a good use for an array? If so, some tips would be helpful. Or is
there a better (easier) approach?

Thanks for your help.