Array Formula help pls.
 

I have different Regions listed in Column F and will enter dates (dd-MMM ) in
Column G when a certain event happens. I would like to count the number of
occurances for each Region only if there is a date in Column G.

I am using the array formula:

=COUNT(IF(F$2:F$133="Region 1",G$2:G$133))

but it returns the number of occurances of "Region 1", instead of counting
the number of times a date shows up in G2 to G133.

Can anyone see what I am doing wrong?

Excel 2000 on XP
cheers,

countif would really work here
one option is Sumproduct()
=sumproduct(--(F$2:F$133="Region 1"),--(G$2:G$133<""))
the --( changes the logical true false to a numeric 1,0. the arrays in each
section must be the same size but cannot be the short hand for full columns
or rows

"klam" wrote:

I have different Regions listed in Column F and will enter dates (dd-MMM ) in
Column G when a certain event happens. I would like to count the number of
occurances for each Region only if there is a date in Column G.

I am using the array formula:

=COUNT(IF(F$2:F$133="Region 1",G$2:G$133))

but it returns the number of occurances of "Region 1", instead of counting
the number of times a date shows up in G2 to G133.

Can anyone see what I am doing wrong?

Excel 2000 on XP
cheers,

bj,

Thx a mint for your suggestion.

It almost works...

Column G will either have text or the date or be blank, and I only want to
count the ones with a date in it. (Sorry I wasn't clearer on that in my
first post.)

How should I modify your formula to just count a value?

cheers,





"bj" wrote:

countif would really work here
one option is Sumproduct()
=sumproduct(--(F$2:F$133="Region 1"),--(G$2:G$133<""))
the --( changes the logical true false to a numeric 1,0. the arrays in each
section must be the same size but cannot be the short hand for full columns
or rows

"klam" wrote:

I have different Regions listed in Column F and will enter dates (dd-MMM ) in
Column G when a certain event happens. I would like to count the number of
occurances for each Region only if there is a date in Column G.

I am using the array formula:

=COUNT(IF(F$2:F$133="Region 1",G$2:G$133))

but it returns the number of occurances of "Region 1", instead of counting
the number of times a date shows up in G2 to G133.

Can anyone see what I am doing wrong?

Excel 2000 on XP
cheers,

try
=sumproduct(--(F$2:F$133="Region 1"),--(isnumber(G$2:G$133)))


"klam" wrote:

bj,

Thx a mint for your suggestion.

It almost works...

Column G will either have text or the date or be blank, and I only want to
count the ones with a date in it. (Sorry I wasn't clearer on that in my
first post.)

How should I modify your formula to just count a value?

cheers,





"bj" wrote:

countif would really work here
one option is Sumproduct()
=sumproduct(--(F$2:F$133="Region 1"),--(G$2:G$133<""))
the --( changes the logical true false to a numeric 1,0. the arrays in each
section must be the same size but cannot be the short hand for full columns
or rows

"klam" wrote:

I have different Regions listed in Column F and will enter dates (dd-MMM ) in
Column G when a certain event happens. I would like to count the number of
occurances for each Region only if there is a date in Column G.

I am using the array formula:

=COUNT(IF(F$2:F$133="Region 1",G$2:G$133))

but it returns the number of occurances of "Region 1", instead of counting
the number of times a date shows up in G2 to G133.

Can anyone see what I am doing wrong?

Excel 2000 on XP
cheers,

Thx bj.

Yes, that worked like a charm.

When I was trying to figure it out for quite a while this morning, I did try
a formula with ISNUMBER, but obviously I didn't pair it correctly. Even
after reading a lot of the Excel help on worksheet functions, things that
sound easy to do end up being complicated formulas (=

thx a mint. cheers,

"bj" wrote:

try
=sumproduct(--(F$2:F$133="Region 1"),--(isnumber(G$2:G$133)))


"klam" wrote:

bj,

Thx a mint for your suggestion.

It almost works...

Column G will either have text or the date or be blank, and I only want to
count the ones with a date in it. (Sorry I wasn't clearer on that in my
first post.)

How should I modify your formula to just count a value?

cheers,





"bj" wrote:

countif would really work here
one option is Sumproduct()
=sumproduct(--(F$2:F$133="Region 1"),--(G$2:G$133<""))
the --( changes the logical true false to a numeric 1,0. the arrays in each
section must be the same size but cannot be the short hand for full columns
or rows

"klam" wrote:

I have different Regions listed in Column F and will enter dates (dd-MMM ) in
Column G when a certain event happens. I would like to count the number of
occurances for each Region only if there is a date in Column G.

I am using the array formula:

=COUNT(IF(F$2:F$133="Region 1",G$2:G$133))

but it returns the number of occurances of "Region 1", instead of counting
the number of times a date shows up in G2 to G133.

Can anyone see what I am doing wrong?

Excel 2000 on XP
cheers,