Formula Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)))
 
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.
 
Perfect!
Thank you very much!

T. Valko said:
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.
Click to expand...
 
Back
Top