Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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))) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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))) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula problem | Excel Worksheet Functions | |||
Formula Problem | New Users to Excel | |||
Formula problem | Excel Discussion (Misc queries) | |||
I have a problem with my Formula. | Excel Worksheet Functions | |||
IF THEN formula problem | Excel Discussion (Misc queries) |