Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with altering a formula
I am using the following formula as part of my data source for a chart and it
works perfectly. It sums column D, rows 2 through 20K where the value in column A, rows 2 through 20K = cell G2 and where the value in column E, rows 2 through 20K = 1. The SUBTOTAL allows for any filtering on my source tab (AAT_Raw_Data) to dynamically get factored in. =SUMPRODUCT(SUBTOTAL(9,OFFSET(AAT_Raw_Data!$D$2:AA T_Raw_Data!$D$20000,ROW(AAT_Raw_Data!$D$2:AAT_Raw_ Data!$D$20000)-ROW(AAT_Raw_Data!$D$2),0,1)),--(AAT_Raw_Data!$A$2:AAT_Raw_Data!$A$20000=G2),--(AAT_Raw_Data!$E$2:AAT_Raw_Data!$E$20000=1)) I need to alter this a bit. I need a count of cells in column B, rows 2 through 20K where the value in column A, rows 2 through 20K = cell G2 and where the value in column D, rows 2 through 20K = 180. I still need to factor in any filtering that might happen on tab (AAT_Raw_Data). It seems the only real difference is that I need a count instead of a sum. A couple of the column references are changing, but that's not what I'm stuck on. Can someone help me with this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with altering a formula
Try this:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(AAT_Raw_Data!B2:B200 00,ROW(AAT_Raw_Data!B2:B20000)-ROW(AAT_Raw_Data!B2),0,1)),--(AAT_Raw_Data!A2:A20000=G2),--(AAT_Raw_Data!D2:D20000=180)) -- Biff Microsoft Excel MVP "cherman" wrote in message ... I am using the following formula as part of my data source for a chart and it works perfectly. It sums column D, rows 2 through 20K where the value in column A, rows 2 through 20K = cell G2 and where the value in column E, rows 2 through 20K = 1. The SUBTOTAL allows for any filtering on my source tab (AAT_Raw_Data) to dynamically get factored in. =SUMPRODUCT(SUBTOTAL(9,OFFSET(AAT_Raw_Data!$D$2:AA T_Raw_Data!$D$20000,ROW(AAT_Raw_Data!$D$2:AAT_Raw_ Data!$D$20000)-ROW(AAT_Raw_Data!$D$2),0,1)),--(AAT_Raw_Data!$A$2:AAT_Raw_Data!$A$20000=G2),--(AAT_Raw_Data!$E$2:AAT_Raw_Data!$E$20000=1)) I need to alter this a bit. I need a count of cells in column B, rows 2 through 20K where the value in column A, rows 2 through 20K = cell G2 and where the value in column D, rows 2 through 20K = 180. I still need to factor in any filtering that might happen on tab (AAT_Raw_Data). It seems the only real difference is that I need a count instead of a sum. A couple of the column references are changing, but that's not what I'm stuck on. Can someone help me with this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with altering a formula
Worked like a c-h-a-r-m!
Much thanks! "T. Valko" wrote: Try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(AAT_Raw_Data!B2:B200 00,ROW(AAT_Raw_Data!B2:B20000)-ROW(AAT_Raw_Data!B2),0,1)),--(AAT_Raw_Data!A2:A20000=G2),--(AAT_Raw_Data!D2:D20000=180)) -- Biff Microsoft Excel MVP "cherman" wrote in message ... I am using the following formula as part of my data source for a chart and it works perfectly. It sums column D, rows 2 through 20K where the value in column A, rows 2 through 20K = cell G2 and where the value in column E, rows 2 through 20K = 1. The SUBTOTAL allows for any filtering on my source tab (AAT_Raw_Data) to dynamically get factored in. =SUMPRODUCT(SUBTOTAL(9,OFFSET(AAT_Raw_Data!$D$2:AA T_Raw_Data!$D$20000,ROW(AAT_Raw_Data!$D$2:AAT_Raw_ Data!$D$20000)-ROW(AAT_Raw_Data!$D$2),0,1)),--(AAT_Raw_Data!$A$2:AAT_Raw_Data!$A$20000=G2),--(AAT_Raw_Data!$E$2:AAT_Raw_Data!$E$20000=1)) I need to alter this a bit. I need a count of cells in column B, rows 2 through 20K where the value in column A, rows 2 through 20K = cell G2 and where the value in column D, rows 2 through 20K = 180. I still need to factor in any filtering that might happen on tab (AAT_Raw_Data). It seems the only real difference is that I need a count instead of a sum. A couple of the column references are changing, but that's not what I'm stuck on. Can someone help me with this? . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with altering a formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "cherman" wrote in message ... Worked like a c-h-a-r-m! Much thanks! "T. Valko" wrote: Try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(AAT_Raw_Data!B2:B200 00,ROW(AAT_Raw_Data!B2:B20000)-ROW(AAT_Raw_Data!B2),0,1)),--(AAT_Raw_Data!A2:A20000=G2),--(AAT_Raw_Data!D2:D20000=180)) -- Biff Microsoft Excel MVP "cherman" wrote in message ... I am using the following formula as part of my data source for a chart and it works perfectly. It sums column D, rows 2 through 20K where the value in column A, rows 2 through 20K = cell G2 and where the value in column E, rows 2 through 20K = 1. The SUBTOTAL allows for any filtering on my source tab (AAT_Raw_Data) to dynamically get factored in. =SUMPRODUCT(SUBTOTAL(9,OFFSET(AAT_Raw_Data!$D$2:AA T_Raw_Data!$D$20000,ROW(AAT_Raw_Data!$D$2:AAT_Raw_ Data!$D$20000)-ROW(AAT_Raw_Data!$D$2),0,1)),--(AAT_Raw_Data!$A$2:AAT_Raw_Data!$A$20000=G2),--(AAT_Raw_Data!$E$2:AAT_Raw_Data!$E$20000=1)) I need to alter this a bit. I need a count of cells in column B, rows 2 through 20K where the value in column A, rows 2 through 20K = cell G2 and where the value in column D, rows 2 through 20K = 180. I still need to factor in any filtering that might happen on tab (AAT_Raw_Data). It seems the only real difference is that I need a count instead of a sum. A couple of the column references are changing, but that's not what I'm stuck on. Can someone help me with this? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Altering text colours using a formula | Excel Discussion (Misc queries) | |||
Move source without altering formula | Excel Discussion (Misc queries) | |||
Altering VLookup Formula | Excel Worksheet Functions | |||
Values won't display after altering a formula. | Excel Worksheet Functions | |||
Formula keeps altering by itself! | Excel Worksheet Functions |