Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct help
this formula works fine. however, I am now looking for a way to include the
entire columns ($G, $J, and $V), instead of just the short range, as the source material so people can add rows to their heart's content, without having to go back an change the formula. Any help would be greatly appreciated. =SUMPRODUCT(('Tracking Sheet - Detail Report'!G2:G34="Claims")*('Tracking Sheet - Detail Report'!J2:J34=C58)*('Tracking Sheet - Detail Report'!V2:V34=1)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct help
If you are using 2007; you can refer this as G:G instead of G2:G65535 but
this will not work in 2003...So better go with the below version...(which is just 1 row less than the max number of rows in 2003) =SUMPRODUCT( ('Tracking Sheet - Detail Report'!G2:G65535="Claims")* ('Tracking Sheet - Detail Report'!J2:J65535=C58)* ('Tracking Sheet - Detail Report'!V2:V65535=1)) If this post helps click Yes --------------- Jacob Skaria "JeffZ88" wrote: this formula works fine. however, I am now looking for a way to include the entire columns ($G, $J, and $V), instead of just the short range, as the source material so people can add rows to their heart's content, without having to go back an change the formula. Any help would be greatly appreciated. =SUMPRODUCT(('Tracking Sheet - Detail Report'!G2:G34="Claims")*('Tracking Sheet - Detail Report'!J2:J34=C58)*('Tracking Sheet - Detail Report'!V2:V34=1)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct help
Hi,
You can reference entire columns using J:J type of references. Some functions do not support these types of references before 2007. You might also consider range names to make your formula simplier: If you named column G -- G and column J -- J and V -- V you could simplify the formula to =SUMPRODUCT((G="Claims")*(J=C58)*(V=1)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JeffZ88" wrote: this formula works fine. however, I am now looking for a way to include the entire columns ($G, $J, and $V), instead of just the short range, as the source material so people can add rows to their heart's content, without having to go back an change the formula. Any help would be greatly appreciated. =SUMPRODUCT(('Tracking Sheet - Detail Report'!G2:G34="Claims")*('Tracking Sheet - Detail Report'!J2:J34=C58)*('Tracking Sheet - Detail Report'!V2:V34=1)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct help
You REALLY do NOT want to do that. Instead use a dummy row and have
insertions above it or better yet, use defined names for your ranges insertnamedefinename colG in the refers to box =offset($g$1,1,0,counta($a:$a),1) look in the help index for OFFSET -- Don Guillett Microsoft MVP Excel SalesAid Software "JeffZ88" wrote in message ... this formula works fine. however, I am now looking for a way to include the entire columns ($G, $J, and $V), instead of just the short range, as the source material so people can add rows to their heart's content, without having to go back an change the formula. Any help would be greatly appreciated. =SUMPRODUCT(('Tracking Sheet - Detail Report'!G2:G34="Claims")*('Tracking Sheet - Detail Report'!J2:J34=C58)*('Tracking Sheet - Detail Report'!V2:V34=1)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct? | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) |