Formula Problem
 

I have a file with 3 worksheets in it. One is my detail sheet, the second one
is my monthly summary sheet, and the third is my customer list. I have
defined a list based on my customer sheet. I have a list box on my summary
sheet that I use to select a customer. What I'm trying to do is count the
number of rows from my detail sheet for the customer selected in my list box
as well as the year,month, and day. Here is the formula I have but doesn't
seem to be working. Can anyone shed some light on what I'm doing wrong here?
I also have a customer called "All" that my users can select to have it count
all customers instead of just selecting one. You will notice that in my
formula. Any help would be greatly appreciated.

My list box is in cell B3 on my summary sheet.
This formula is in cell B9 of my summary sheet. In column A I have each day
of the month starting from cell A9 thru A31.

=IF($B$3="All",SUMPRODUCT(--(Detail!$J$2:$J$5020=DATE(YEAR($A9),MONTH($A9),DAY ($A9))),--(Detail!$K$2:$K$5020=1),--(Detail!$B$2:$B$50000<"All")),SUMPRODUCT(--(Detail!$J$2:$J$5020=DATE(YEAR($A9),MONTH($A9),DAY ($A9))),--(Detail!$K$2:$K$5020=1),--(Detail!$B$2:$B$50000=Summary!$B$3)))

Formula Problem
 

The problem is in these arrays:

--(Detail!$B$2:$B$50000<"All")
--(Detail!$B$2:$B$50000=Summary!$B$3)

*All* of the arrays must be the same size.

Some are 2:5020 and those above are 2:50000

Try making them *all* 2:5020.

--
Biff
Microsoft Excel MVP


"Secret Squirrel" wrote in
message ...
I have a file with 3 worksheets in it. One is my detail sheet, the second
one
is my monthly summary sheet, and the third is my customer list. I have
defined a list based on my customer sheet. I have a list box on my summary
sheet that I use to select a customer. What I'm trying to do is count the
number of rows from my detail sheet for the customer selected in my list
box
as well as the year,month, and day. Here is the formula I have but doesn't
seem to be working. Can anyone shed some light on what I'm doing wrong
here?
I also have a customer called "All" that my users can select to have it
count
all customers instead of just selecting one. You will notice that in my
formula. Any help would be greatly appreciated.

My list box is in cell B3 on my summary sheet.
This formula is in cell B9 of my summary sheet. In column A I have each
day
of the month starting from cell A9 thru A31.

=IF($B$3="All",SUMPRODUCT(--(Detail!$J$2:$J$5020=DATE(YEAR($A9),MONTH($A9),DAY ($A9))),--(Detail!$K$2:$K$5020=1),--(Detail!$B$2:$B$50000<"All")),SUMPRODUCT(--(Detail!$J$2:$J$5020=DATE(YEAR($A9),MONTH($A9),DAY ($A9))),--(Detail!$K$2:$K$5020=1),--(Detail!$B$2:$B$50000=Summary!$B$3)))

Formula Problem
 

Perfect!
Thank you very much!

"T. Valko" wrote:

The problem is in these arrays:

--(Detail!$B$2:$B$50000<"All")
--(Detail!$B$2:$B$50000=Summary!$B$3)

*All* of the arrays must be the same size.

Some are 2:5020 and those above are 2:50000

Try making them *all* 2:5020.

--
Biff
Microsoft Excel MVP


"Secret Squirrel" wrote in
message ...
I have a file with 3 worksheets in it. One is my detail sheet, the second
one
is my monthly summary sheet, and the third is my customer list. I have
defined a list based on my customer sheet. I have a list box on my summary
sheet that I use to select a customer. What I'm trying to do is count the
number of rows from my detail sheet for the customer selected in my list
box
as well as the year,month, and day. Here is the formula I have but doesn't
seem to be working. Can anyone shed some light on what I'm doing wrong
here?
I also have a customer called "All" that my users can select to have it
count
all customers instead of just selecting one. You will notice that in my
formula. Any help would be greatly appreciated.

My list box is in cell B3 on my summary sheet.
This formula is in cell B9 of my summary sheet. In column A I have each
day
of the month starting from cell A9 thru A31.

=IF($B$3="All",SUMPRODUCT(--(Detail!$J$2:$J$5020=DATE(YEAR($A9),MONTH($A9),DAY ($A9))),--(Detail!$K$2:$K$5020=1),--(Detail!$B$2:$B$50000<"All")),SUMPRODUCT(--(Detail!$J$2:$J$5020=DATE(YEAR($A9),MONTH($A9),DAY ($A9))),--(Detail!$K$2:$K$5020=1),--(Detail!$B$2:$B$50000=Summary!$B$3)))

Formula Problem
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Secret Squirrel" wrote in
message ...
Perfect!
Thank you very much!

"T. Valko" wrote:

The problem is in these arrays:

--(Detail!$B$2:$B$50000<"All")
--(Detail!$B$2:$B$50000=Summary!$B$3)

*All* of the arrays must be the same size.

Some are 2:5020 and those above are 2:50000

Try making them *all* 2:5020.

--
Biff
Microsoft Excel MVP


"Secret Squirrel" wrote in
message ...
I have a file with 3 worksheets in it. One is my detail sheet, the
second
one
is my monthly summary sheet, and the third is my customer list. I have
defined a list based on my customer sheet. I have a list box on my
summary
sheet that I use to select a customer. What I'm trying to do is count
the
number of rows from my detail sheet for the customer selected in my
list
box
as well as the year,month, and day. Here is the formula I have but
doesn't
seem to be working. Can anyone shed some light on what I'm doing wrong
here?
I also have a customer called "All" that my users can select to have it
count
all customers instead of just selecting one. You will notice that in my
formula. Any help would be greatly appreciated.

My list box is in cell B3 on my summary sheet.
This formula is in cell B9 of my summary sheet. In column A I have each
day
of the month starting from cell A9 thru A31.

=IF($B$3="All",SUMPRODUCT(--(Detail!$J$2:$J$5020=DATE(YEAR($A9),MONTH($A9),DAY ($A9))),--(Detail!$K$2:$K$5020=1),--(Detail!$B$2:$B$50000<"All")),SUMPRODUCT(--(Detail!$J$2:$J$5020=DATE(YEAR($A9),MONTH($A9),DAY ($A9))),--(Detail!$K$2:$K$5020=1),--(Detail!$B$2:$B$50000=Summary!$B$3)))