Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
I don't know if this should be done in VBA or a nested IF formula.
I need to produce Monthly and Quarterly totals based on records in a master SS with named columns/fields. I have a second SS also with named columns/fields where all calculations are performed and it is that SS that I use to obtain the results for my report SS. I found that SUMIF wanted an array range which DID NOT not allow full column selection. The number of records varies so a fixed range is not very useful. I have the following formula that works (well kind of) but instead of producing a total for the SELECTED records it results in a total of ALL records. Would like some feed back as to whether this seemingly simple task can be done with a formula or whether I should work on it with code? =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Should SUM just those records selected for a given month number in the named field "RptMonth" (5) matches the value in "MonthClose" AND ALSO has the "Status" field value of "A" that have a "RptYear" value of "2007" and then sums the values in the "WeightedNet" field for just those records. Instead this sums ALL records INSTEAD of just the selectedrecords. Thanks in advance for your suggestions, Dennis |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
I'd use SUMPRODUCT for this
=SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = 2007),(WeightedNet)) This assumes all arrays are the same length. HTH, Barb Reinhardt MonthClose=RptMonth)*(Status="A")*(RptYear="2007") ,WeightedNet)) "DbMstr" wrote: I don't know if this should be done in VBA or a nested IF formula. I need to produce Monthly and Quarterly totals based on records in a master SS with named columns/fields. I have a second SS also with named columns/fields where all calculations are performed and it is that SS that I use to obtain the results for my report SS. I found that SUMIF wanted an array range which DID NOT not allow full column selection. The number of records varies so a fixed range is not very useful. I have the following formula that works (well kind of) but instead of producing a total for the SELECTED records it results in a total of ALL records. Would like some feed back as to whether this seemingly simple task can be done with a formula or whether I should work on it with code? =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Should SUM just those records selected for a given month number in the named field "RptMonth" (5) matches the value in "MonthClose" AND ALSO has the "Status" field value of "A" that have a "RptYear" value of "2007" and then sums the values in the "WeightedNet" field for just those records. Instead this sums ALL records INSTEAD of just the selectedrecords. Thanks in advance for your suggestions, Dennis |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
Dennis,
asusming that the Rpt Yr is on text format...some with "2007" * u may have forgotten to press ctrl+shft+ent..while on the formulated cell... regards, driller -- ***** birds of the same feather flock together.. "DbMstr" wrote: I don't know if this should be done in VBA or a nested IF formula. I need to produce Monthly and Quarterly totals based on records in a master SS with named columns/fields. I have a second SS also with named columns/fields where all calculations are performed and it is that SS that I use to obtain the results for my report SS. I found that SUMIF wanted an array range which DID NOT not allow full column selection. The number of records varies so a fixed range is not very useful. I have the following formula that works (well kind of) but instead of producing a total for the SELECTED records it results in a total of ALL records. Would like some feed back as to whether this seemingly simple task can be done with a formula or whether I should work on it with code? =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Should SUM just those records selected for a given month number in the named field "RptMonth" (5) matches the value in "MonthClose" AND ALSO has the "Status" field value of "A" that have a "RptYear" value of "2007" and then sums the values in the "WeightedNet" field for just those records. Instead this sums ALL records INSTEAD of just the selectedrecords. Thanks in advance for your suggestions, Dennis |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
based on the original sumif formula, w/o CSE , where the first result is a totalsum, assuming TOTAL SUM <0, this could also be with something like this... =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = "2007"),(WeightedNet)) regards, driller -- ***** birds of the same feather flock together.. "Barb Reinhardt" wrote: I'd use SUMPRODUCT for this =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = 2007),(WeightedNet)) This assumes all arrays are the same length. HTH, Barb Reinhardt MonthClose=RptMonth)*(Status="A")*(RptYear="2007") ,WeightedNet)) "DbMstr" wrote: I don't know if this should be done in VBA or a nested IF formula. I need to produce Monthly and Quarterly totals based on records in a master SS with named columns/fields. I have a second SS also with named columns/fields where all calculations are performed and it is that SS that I use to obtain the results for my report SS. I found that SUMIF wanted an array range which DID NOT not allow full column selection. The number of records varies so a fixed range is not very useful. I have the following formula that works (well kind of) but instead of producing a total for the SELECTED records it results in a total of ALL records. Would like some feed back as to whether this seemingly simple task can be done with a formula or whether I should work on it with code? =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Should SUM just those records selected for a given month number in the named field "RptMonth" (5) matches the value in "MonthClose" AND ALSO has the "Status" field value of "A" that have a "RptYear" value of "2007" and then sums the values in the "WeightedNet" field for just those records. Instead this sums ALL records INSTEAD of just the selectedrecords. Thanks in advance for your suggestions, Dennis |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
On Jun 26, 3:24 pm, driller wrote:
based on the original sumif formula, w/o CSE , where the first result is a totalsum, assuming TOTAL SUM <0, this could also be with something like this... =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = "2007"),(WeightedNet)) regards, driller -- ***** birds of the same feather flock together.. "Barb Reinhardt" wrote: I'd use SUMPRODUCT for this =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = 2007),(WeightedNet)) This assumes all arrays are the same length. HTH, Barb Reinhardt MonthClose=RptMonth)*(Status="A")*(RptYear="2007") ,WeightedNet)) "DbMstr" wrote: I don't know if this should be done in VBA or a nested IF formula. I need to produce Monthly and Quarterly totals based on records in a master SS with named columns/fields. I have a second SS also with named columns/fields where all calculations are performed and it is that SS that I use to obtain the results for my report SS. I found that SUMIF wanted an array range which DID NOT not allow full column selection. The number of records varies so a fixed range is not very useful. I have the following formula that works (well kind of) but instead of producing a total for the SELECTED records it results in a total of ALL records. Would like some feed back as to whether this seemingly simple task can be done with a formula or whether I should work on it with code? =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Should SUM just those records selected for a given month number in the named field "RptMonth" (5) matches the value in "MonthClose" AND ALSO has the "Status" field value of "A" that have a "RptYear" value of "2007" and then sums the values in the "WeightedNet" field for just those records. Instead this sums ALL records INSTEAD of just the selectedrecords. Thanks in advance for your suggestions, Dennis- Hide quoted text - - Show quoted text - I had tried the SUMPRODUCT but apparently not correctly. This seems to work if I restrict the range to a fixed range K2:K89, rather than using named columns such as MonthClose I didn't need to quote the year since I created it using INT((YEAR(MonthYYClose) It doesn't work if I take out the "-" sign in front of each but I can certainly *-1 to get a positive result =SUMPRODUCT(-(K2:K89=5),-(J2:J89="A"),-(L2:L89 = 2007),(I2:I89))*-1 So now I have to figure out how to define a range that will vary depending upon the number of records in the SS. This test has 89 records but I will have a few thouand when completed. I will need a range that starts for example at K2: and stops at the last record, for example K2140???? Thanks for the suggestions, any more are greatly appreciated, Dennis |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
Assuming you're not using XL07 with over a million rows, simply revise your
named ranges to a size that will account for future expansion. Say MonthClose equals K2 to K5000 or K15000. Just make sure that all ranges are equal in size. As to that minus sign. Did you think that the *double unary* ( -- ) was a typo? Leave those *double* minuses in the formula! They cancel out and return the proper sign at calculation completion. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "DbMstr" wrote in message ups.com... On Jun 26, 3:24 pm, driller wrote: based on the original sumif formula, w/o CSE , where the first result is a totalsum, assuming TOTAL SUM <0, this could also be with something like this... =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = "2007"),(WeightedNet)) regards, driller -- ***** birds of the same feather flock together.. "Barb Reinhardt" wrote: I'd use SUMPRODUCT for this =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = 2007),(WeightedNet)) This assumes all arrays are the same length. HTH, Barb Reinhardt MonthClose=RptMonth)*(Status="A")*(RptYear="2007") ,WeightedNet)) "DbMstr" wrote: I don't know if this should be done in VBA or a nested IF formula. I need to produce Monthly and Quarterly totals based on records in a master SS with named columns/fields. I have a second SS also with named columns/fields where all calculations are performed and it is that SS that I use to obtain the results for my report SS. I found that SUMIF wanted an array range which DID NOT not allow full column selection. The number of records varies so a fixed range is not very useful. I have the following formula that works (well kind of) but instead of producing a total for the SELECTED records it results in a total of ALL records. Would like some feed back as to whether this seemingly simple task can be done with a formula or whether I should work on it with code? =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Should SUM just those records selected for a given month number in the named field "RptMonth" (5) matches the value in "MonthClose" AND ALSO has the "Status" field value of "A" that have a "RptYear" value of "2007" and then sums the values in the "WeightedNet" field for just those records. Instead this sums ALL records INSTEAD of just the selectedrecords. Thanks in advance for your suggestions, Dennis- Hide quoted text - - Show quoted text - I had tried the SUMPRODUCT but apparently not correctly. This seems to work if I restrict the range to a fixed range K2:K89, rather than using named columns such as MonthClose I didn't need to quote the year since I created it using INT((YEAR(MonthYYClose) It doesn't work if I take out the "-" sign in front of each but I can certainly *-1 to get a positive result =SUMPRODUCT(-(K2:K89=5),-(J2:J89="A"),-(L2:L89 = 2007),(I2:I89))*-1 So now I have to figure out how to define a range that will vary depending upon the number of records in the SS. This test has 89 records but I will have a few thouand when completed. I will need a range that starts for example at K2: and stops at the last record, for example K2140???? Thanks for the suggestions, any more are greatly appreciated, Dennis |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
Hi
There needs to be a double unary minus in front of each expression, not a single minus. The double minus -- coerces the results of the expression for True to 1 and False to 0. alternatively, you could use =SUMPRODUCT((MonthClose=RptMonth)*(Status="A")*(Rp tYear ="2007")*WeightedNet) as far as your Dynamic Ranges are concerned, use the same column for the Count of rows, to ensure they are of equal length. Choose the column that you know will be populated for each row, e.g. Monthclose Refers to=OFFSET($A$1,0,0,COUNTA($A:$A)) Status Refers to=OFFSET($B$1,0,0,COUNTA($A:$A)) ReptYR Refers to=OFFSET($C$1,0,0,COUNTA($A:$A)) -- Regards Roger Govier "DbMstr" wrote in message ups.com... On Jun 26, 3:24 pm, driller wrote: based on the original sumif formula, w/o CSE , where the first result is a totalsum, assuming TOTAL SUM <0, this could also be with something like this... =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = "2007"),(WeightedNet)) regards, driller -- ***** birds of the same feather flock together.. "Barb Reinhardt" wrote: I'd use SUMPRODUCT for this =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = 2007),(WeightedNet)) This assumes all arrays are the same length. HTH, Barb Reinhardt MonthClose=RptMonth)*(Status="A")*(RptYear="2007") ,WeightedNet)) "DbMstr" wrote: I don't know if this should be done in VBA or a nested IF formula. I need to produce Monthly and Quarterly totals based on records in a master SS with named columns/fields. I have a second SS also with named columns/fields where all calculations are performed and it is that SS that I use to obtain the results for my report SS. I found that SUMIF wanted an array range which DID NOT not allow full column selection. The number of records varies so a fixed range is not very useful. I have the following formula that works (well kind of) but instead of producing a total for the SELECTED records it results in a total of ALL records. Would like some feed back as to whether this seemingly simple task can be done with a formula or whether I should work on it with code? =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Should SUM just those records selected for a given month number in the named field "RptMonth" (5) matches the value in "MonthClose" AND ALSO has the "Status" field value of "A" that have a "RptYear" value of "2007" and then sums the values in the "WeightedNet" field for just those records. Instead this sums ALL records INSTEAD of just the selectedrecords. Thanks in advance for your suggestions, Dennis- Hide quoted text - - Show quoted text - I had tried the SUMPRODUCT but apparently not correctly. This seems to work if I restrict the range to a fixed range K2:K89, rather than using named columns such as MonthClose I didn't need to quote the year since I created it using INT((YEAR(MonthYYClose) It doesn't work if I take out the "-" sign in front of each but I can certainly *-1 to get a positive result =SUMPRODUCT(-(K2:K89=5),-(J2:J89="A"),-(L2:L89 = 2007),(I2:I89))*-1 So now I have to figure out how to define a range that will vary depending upon the number of records in the SS. This test has 89 records but I will have a few thouand when completed. I will need a range that starts for example at K2: and stops at the last record, for example K2140???? Thanks for the suggestions, any more are greatly appreciated, Dennis |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
thats a good one for naming dynamic ranges..with the sumproduct below...
=SUMPRODUCT((MonthClose=RptMonth)*(Status="A")*(Rp tYear ="2007")*WeightedNet) from OP original post =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Instead this sums ALL records INSTEAD of just the selectedrecords. im confused and wonder how OP obtain a sort of ALL result with "2007" in the sum(if when RptYear is DONE as numeric from INT function.. regards, driller -- ***** birds of the same feather flock together.. "Roger Govier" wrote: Hi There needs to be a double unary minus in front of each expression, not a single minus. The double minus -- coerces the results of the expression for True to 1 and False to 0. alternatively, you could use =SUMPRODUCT((MonthClose=RptMonth)*(Status="A")*(Rp tYear ="2007")*WeightedNet) as far as your Dynamic Ranges are concerned, use the same column for the Count of rows, to ensure they are of equal length. Choose the column that you know will be populated for each row, e.g. Monthclose Refers to=OFFSET($A$1,0,0,COUNTA($A:$A)) Status Refers to=OFFSET($B$1,0,0,COUNTA($A:$A)) ReptYR Refers to=OFFSET($C$1,0,0,COUNTA($A:$A)) -- Regards Roger Govier "DbMstr" wrote in message ups.com... On Jun 26, 3:24 pm, driller wrote: based on the original sumif formula, w/o CSE , where the first result is a totalsum, assuming TOTAL SUM <0, this could also be with something like this... =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = "2007"),(WeightedNet)) regards, driller -- ***** birds of the same feather flock together.. "Barb Reinhardt" wrote: I'd use SUMPRODUCT for this =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = 2007),(WeightedNet)) This assumes all arrays are the same length. HTH, Barb Reinhardt MonthClose=RptMonth)*(Status="A")*(RptYear="2007") ,WeightedNet)) "DbMstr" wrote: I don't know if this should be done in VBA or a nested IF formula. I need to produce Monthly and Quarterly totals based on records in a master SS with named columns/fields. I have a second SS also with named columns/fields where all calculations are performed and it is that SS that I use to obtain the results for my report SS. I found that SUMIF wanted an array range which DID NOT not allow full column selection. The number of records varies so a fixed range is not very useful. I have the following formula that works (well kind of) but instead of producing a total for the SELECTED records it results in a total of ALL records. Would like some feed back as to whether this seemingly simple task can be done with a formula or whether I should work on it with code? =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Should SUM just those records selected for a given month number in the named field "RptMonth" (5) matches the value in "MonthClose" AND ALSO has the "Status" field value of "A" that have a "RptYear" value of "2007" and then sums the values in the "WeightedNet" field for just those records. Instead this sums ALL records INSTEAD of just the selectedrecords. Thanks in advance for your suggestions, Dennis- Hide quoted text - - Show quoted text - I had tried the SUMPRODUCT but apparently not correctly. This seems to work if I restrict the range to a fixed range K2:K89, rather than using named columns such as MonthClose I didn't need to quote the year since I created it using INT((YEAR(MonthYYClose) It doesn't work if I take out the "-" sign in front of each but I can certainly *-1 to get a positive result =SUMPRODUCT(-(K2:K89=5),-(J2:J89="A"),-(L2:L89 = 2007),(I2:I89))*-1 So now I have to figure out how to define a range that will vary depending upon the number of records in the SS. This test has 89 records but I will have a few thouand when completed. I will need a range that starts for example at K2: and stops at the last record, for example K2140???? Thanks for the suggestions, any more are greatly appreciated, Dennis |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
On Jun 26, 6:26 pm, driller wrote:
thats a good one for naming dynamic ranges..with the sumproduct below... =SUMPRODUCT((MonthClose=RptMonth)*(Status="A")*(Rp tYear ="2007")*WeightedNet) from OP original post =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Instead this sums ALL records INSTEAD of just the selectedrecords. im confused and wonder how OP obtain a sort of ALL result with "2007" in the sum(if when RptYear is DONE as numeric from INT function.. regards, driller -- ***** birds of the same feather flock together.. "Roger Govier" wrote: Hi There needs to be a double unary minus in front of each expression, not a single minus. The double minus -- coerces the results of the expression for True to 1 and False to 0. alternatively, you could use =SUMPRODUCT((MonthClose=RptMonth)*(Status="A")*(Rp tYear ="2007")*WeightedNet) as far as your Dynamic Ranges are concerned, use the same column for the Count of rows, to ensure they are of equal length. Choose the column that you know will be populated for each row, e.g. Monthclose Refers to=OFFSET($A$1,0,0,COUNTA($A:$A)) Status Refers to=OFFSET($B$1,0,0,COUNTA($A:$A)) ReptYR Refers to=OFFSET($C$1,0,0,COUNTA($A:$A)) -- Regards Roger Govier "DbMstr" wrote in message oups.com... On Jun 26, 3:24 pm, driller wrote: based on the original sumif formula, w/o CSE , where the first result is a totalsum, assuming TOTAL SUM <0, this could also be with something like this... =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = "2007"),(WeightedNet)) regards, driller -- ***** birds of the same feather flock together.. "Barb Reinhardt" wrote: I'd use SUMPRODUCT for this =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = 2007),(WeightedNet)) This assumes all arrays are the same length. HTH, Barb Reinhardt MonthClose=RptMonth)*(Status="A")*(RptYear="2007") ,WeightedNet)) "DbMstr" wrote: I don't know if this should be done in VBA or a nested IF formula. I need to produce Monthly and Quarterly totals based on records in a master SS with named columns/fields. I have a second SS also with named columns/fields where all calculations are performed and it is that SS that I use to obtain the results for my report SS. I found that SUMIF wanted an array range which DID NOT not allow full column selection. The number of records varies so a fixed range is not very useful. I have the following formula that works (well kind of) but instead of producing a total for the SELECTED records it results in a total of ALL records. Would like some feed back as to whether this seemingly simple task can be done with a formula or whether I should work on it with code? =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Should SUM just those records selected for a given month number in the named field "RptMonth" (5) matches the value in "MonthClose" AND ALSO has the "Status" field value of "A" that have a "RptYear" value of "2007" and then sums the values in the "WeightedNet" field for just those records. Instead this sums ALL records INSTEAD of just the selectedrecords. Thanks in advance for your suggestions, Dennis- Hide quoted text - - Show quoted text - I had tried the SUMPRODUCT but apparently not correctly. This seems to work if I restrict the range to a fixed range K2:K89, rather than using named columns such as MonthClose I didn't need to quote the year since I created it using INT((YEAR(MonthYYClose) It doesn't work if I take out the "-" sign in front of each but I can certainly *-1 to get a positive result =SUMPRODUCT(-(K2:K89=5),-(J2:J89="A"),-(L2:L89 = 2007),(I2:I89))*-1 So now I have to figure out how to define a range that will vary depending upon the number of records in the SS. This test has 89 records but I will have a few thouand when completed. I will need a range that starts for example at K2: and stops at the last record, for example K2140???? Thanks for the suggestions, any more are greatly appreciated, Dennis- Hide quoted text - - Show quoted text - It's the SUMIF that returns ALL records IF you haven't made sure that the number of records are the same. For example=SUM(IF((K2:K89=5),I2:I90)) NOTE that the I90 ending is different than the K89. Even if simplified to one criteria. If entered as an array formula then {=SUM(IF((K2:K89=5),I2:I89))} equals the correct test total. $12,000 If NOT an array then =SUM(IF((K2:K89=5),I2:I89)) equals the total of ALL records. $450,000 Right, thanks people, we need the double "--" in front of the SUMPRODUCT ranges. If single "-" then need to multiply *-1 to make positive. The SUMPRODUCT seems to be a bit more versitile as it does not need to be an array formula to function. However it too will NOT accept a named column such as "MonthClose" in place of a limited range such as $K$2:$K$10000. Both will accept a named cell for a variable. So have to redesign IF I don't make the range big enough to handle the recordset which can grow over time. I felt sure that the beauty of using the named range in the formula would allow the SUMIF, SUM(IF( or the SUMPRODUCT formulas to accept any number of records up to 65K in 2003. Apparently NOT. The SUMPRODUCT did not support multiple choices for a single column range, for example summing for EITHER a Status value of "A" or "B" but worked just fine when I added the whole formula to itself with just the change of the Status value. Thanks everyone for your help and kind suggestions. I have something I can work with though it still presents some barriers. I'm big on named cells, named fields and named records so nothing gets broken in my formulas if I delete a record or column/field. Multiple variables reporting would have been much simpler in Access but client insisting on Excel and I have worked with it since DAY 1 but always more to learn. What fun. Dennis |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
However it too will NOT accept a named column such as "MonthClose"
No, you cannot use whole columns as arguments in Sumproduct, other than in XL2007. You can use A1:A65535, or A2:A65536 i.e. just one short of the whole column. You have been given a method for creating a Dynamic range, which will grow to accommodate your range of data. The SUMPRODUCT did not support multiple choices for a single column range, for example summing for EITHER a Status value of "A" or "B" Yes it does. Try =SUMPRODUCT(--(A1:A10={"a","b"}),B1:B10) To understand more about Sumproduct, read Bob Phillips discussion document http://xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "DbMstr" wrote in message ups.com... On Jun 26, 6:26 pm, driller wrote: thats a good one for naming dynamic ranges..with the sumproduct below... =SUMPRODUCT((MonthClose=RptMonth)*(Status="A")*(Rp tYear ="2007")*WeightedNet) from OP original post =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Instead this sums ALL records INSTEAD of just the selectedrecords. im confused and wonder how OP obtain a sort of ALL result with "2007" in the sum(if when RptYear is DONE as numeric from INT function.. regards, driller -- ***** birds of the same feather flock together.. "Roger Govier" wrote: Hi There needs to be a double unary minus in front of each expression, not a single minus. The double minus -- coerces the results of the expression for True to 1 and False to 0. alternatively, you could use =SUMPRODUCT((MonthClose=RptMonth)*(Status="A")*(Rp tYear ="2007")*WeightedNet) as far as your Dynamic Ranges are concerned, use the same column for the Count of rows, to ensure they are of equal length. Choose the column that you know will be populated for each row, e.g. Monthclose Refers to=OFFSET($A$1,0,0,COUNTA($A:$A)) Status Refers to=OFFSET($B$1,0,0,COUNTA($A:$A)) ReptYR Refers to=OFFSET($C$1,0,0,COUNTA($A:$A)) -- Regards Roger Govier "DbMstr" wrote in message oups.com... On Jun 26, 3:24 pm, driller wrote: based on the original sumif formula, w/o CSE , where the first result is a totalsum, assuming TOTAL SUM <0, this could also be with something like this... =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = "2007"),(WeightedNet)) regards, driller -- ***** birds of the same feather flock together.. "Barb Reinhardt" wrote: I'd use SUMPRODUCT for this =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = 2007),(WeightedNet)) This assumes all arrays are the same length. HTH, Barb Reinhardt MonthClose=RptMonth)*(Status="A")*(RptYear="2007") ,WeightedNet)) "DbMstr" wrote: I don't know if this should be done in VBA or a nested IF formula. I need to produce Monthly and Quarterly totals based on records in a master SS with named columns/fields. I have a second SS also with named columns/fields where all calculations are performed and it is that SS that I use to obtain the results for my report SS. I found that SUMIF wanted an array range which DID NOT not allow full column selection. The number of records varies so a fixed range is not very useful. I have the following formula that works (well kind of) but instead of producing a total for the SELECTED records it results in a total of ALL records. Would like some feed back as to whether this seemingly simple task can be done with a formula or whether I should work on it with code? =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Should SUM just those records selected for a given month number in the named field "RptMonth" (5) matches the value in "MonthClose" AND ALSO has the "Status" field value of "A" that have a "RptYear" value of "2007" and then sums the values in the "WeightedNet" field for just those records. Instead this sums ALL records INSTEAD of just the selectedrecords. Thanks in advance for your suggestions, Dennis- Hide quoted text - - Show quoted text - I had tried the SUMPRODUCT but apparently not correctly. This seems to work if I restrict the range to a fixed range K2:K89, rather than using named columns such as MonthClose I didn't need to quote the year since I created it using INT((YEAR(MonthYYClose) It doesn't work if I take out the "-" sign in front of each but I can certainly *-1 to get a positive result =SUMPRODUCT(-(K2:K89=5),-(J2:J89="A"),-(L2:L89 = 2007),(I2:I89))*-1 So now I have to figure out how to define a range that will vary depending upon the number of records in the SS. This test has 89 records but I will have a few thouand when completed. I will need a range that starts for example at K2: and stops at the last record, for example K2140???? Thanks for the suggestions, any more are greatly appreciated, Dennis- Hide quoted text - - Show quoted text - It's the SUMIF that returns ALL records IF you haven't made sure that the number of records are the same. For example=SUM(IF((K2:K89=5),I2:I90)) NOTE that the I90 ending is different than the K89. Even if simplified to one criteria. If entered as an array formula then {=SUM(IF((K2:K89=5),I2:I89))} equals the correct test total. $12,000 If NOT an array then =SUM(IF((K2:K89=5),I2:I89)) equals the total of ALL records. $450,000 Right, thanks people, we need the double "--" in front of the SUMPRODUCT ranges. If single "-" then need to multiply *-1 to make positive. The SUMPRODUCT seems to be a bit more versitile as it does not need to be an array formula to function. However it too will NOT accept a named column such as "MonthClose" in place of a limited range such as $K$2:$K$10000. Both will accept a named cell for a variable. So have to redesign IF I don't make the range big enough to handle the recordset which can grow over time. I felt sure that the beauty of using the named range in the formula would allow the SUMIF, SUM(IF( or the SUMPRODUCT formulas to accept any number of records up to 65K in 2003. Apparently NOT. The SUMPRODUCT did not support multiple choices for a single column range, for example summing for EITHER a Status value of "A" or "B" but worked just fine when I added the whole formula to itself with just the change of the Status value. Thanks everyone for your help and kind suggestions. I have something I can work with though it still presents some barriers. I'm big on named cells, named fields and named records so nothing gets broken in my formulas if I delete a record or column/field. Multiple variables reporting would have been much simpler in Access but client insisting on Excel and I have worked with it since DAY 1 but always more to learn. What fun. Dennis |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
On Jun 26, 11:53 pm, "Roger Govier"
wrote: However it too will NOT accept a named column such as "MonthClose" No, you cannot use whole columns as arguments in Sumproduct, other than in XL2007. You can use A1:A65535, or A2:A65536 i.e. just one short of the whole column. You have been given a method for creating a Dynamic range, which will grow to accommodate your range of data. The SUMPRODUCT did not support multiple choices for a single column range, for example summing for EITHER a Status value of "A" or "B" Yes it does. Try =SUMPRODUCT(--(A1:A10={"a","b"}),B1:B10) To understand more about Sumproduct, read Bob Phillips discussion documenthttp://xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "DbMstr" wrote in message ups.com... On Jun 26, 6:26 pm, driller wrote: thats a good one for naming dynamic ranges..with the sumproduct below... =SUMPRODUCT((MonthClose=RptMonth)*(Status="A")*(Rp tYear ="2007")*WeightedNet) from OP original post =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Instead this sums ALL records INSTEAD of just the selectedrecords. im confused and wonder how OP obtain a sort of ALL result with "2007" in the sum(if when RptYear is DONE as numeric from INT function.. regards, driller -- ***** birds of the same feather flock together.. "Roger Govier" wrote: Hi There needs to be a double unary minus in front of each expression, not a single minus. The double minus -- coerces the results of the expression for True to 1 and False to 0. alternatively, you could use =SUMPRODUCT((MonthClose=RptMonth)*(Status="A")*(Rp tYear ="2007")*WeightedNet) as far as your Dynamic Ranges are concerned, use the same column for the Count of rows, to ensure they are of equal length. Choose the column that you know will be populated for each row, e.g. Monthclose Refers to=OFFSET($A$1,0,0,COUNTA($A:$A)) Status Refers to=OFFSET($B$1,0,0,COUNTA($A:$A)) ReptYR Refers to=OFFSET($C$1,0,0,COUNTA($A:$A)) -- Regards Roger Govier "DbMstr" wrote in message oups.com... On Jun 26, 3:24 pm, driller wrote: based on the original sumif formula, w/o CSE , where the first result is a totalsum, assuming TOTAL SUM <0, this could also be with something like this... =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = "2007"),(WeightedNet)) regards, driller -- ***** birds of the same feather flock together.. "Barb Reinhardt" wrote: I'd use SUMPRODUCT for this =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = 2007),(WeightedNet)) This assumes all arrays are the same length. HTH, Barb Reinhardt MonthClose=RptMonth)*(Status="A")*(RptYear="2007") ,WeightedNet)) "DbMstr" wrote: I don't know if this should be done in VBA or a nested IF formula. I need to produce Monthly and Quarterly totals based on records in a master SS with named columns/fields. I have a second SS also with named columns/fields where all calculations are performed and it is that SS that I use to obtain the results for my report SS. I found that SUMIF wanted an array range which DID NOT not allow full column selection. The number of records varies so a fixed range is not very useful. I have the following formula that works (well kind of) but instead of producing a total for the SELECTED records it results in a total of ALL records. Would like some feed back as to whether this seemingly simple task can be done with a formula or whether I should work on it with code? =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Should SUM just those records selected for a given month number in the named field "RptMonth" (5) matches the value in "MonthClose" AND ALSO has the "Status" field value of "A" that have a "RptYear" value of "2007" and then sums the values in the "WeightedNet" field for just those records. Instead this sums ALL records INSTEAD of just the selectedrecords. Thanks in advance for your suggestions, Dennis- Hide quoted text - - Show quoted text - I had tried the SUMPRODUCT but apparently not correctly. This seems to work if I restrict the range to a fixed range K2:K89, rather than using named columns such as MonthClose I didn't need to quote the year since I created it using INT((YEAR(MonthYYClose) It doesn't work if I take out the "-" sign in front of each but I can certainly *-1 to get a positive result =SUMPRODUCT(-(K2:K89=5),-(J2:J89="A"),-(L2:L89 = 2007),(I2:I89))*-1 So now I have to figure out how to define a range that will vary depending upon the number of records in the SS. This test has 89 records but I will have a few thouand when completed. I will need a range that starts for example at K2: and stops at the last record, for example K2140???? Thanks for the suggestions, any more are greatly appreciated, Dennis- Hide quoted text - - Show quoted text - It's the SUMIF that returns ALL records IF you haven't made sure that the number of records are the same. For example=SUM(IF((K2:K89=5),I2:I90)) NOTE that the I90 ending is different than the K89. Even if simplified to one criteria. If entered as an array formula then {=SUM(IF((K2:K89=5),I2:I89))} equals the correct test total. $12,000 If NOT an array then =SUM(IF((K2:K89=5),I2:I89)) equals the total of ALL records. $450,000 Right, thanks people, we need the double "--" in front of the SUMPRODUCT ranges. If single "-" then need to multiply *-1 to make positive. The SUMPRODUCT seems to be a bit more versitile as it does not need to be an array formula to function. However it too will NOT accept a named column such as "MonthClose" in place of a limited range such as $K$2:$K$10000. Both will accept a named cell for a variable. So have to redesign IF I don't make the range big enough to handle the recordset which can grow over time. I felt sure that the beauty of using the named range in the formula would allow the SUMIF, SUM(IF( or the SUMPRODUCT formulas to accept any number of records up to 65K in 2003. Apparently NOT. The SUMPRODUCT did not support multiple choices for a single column range, for example summing for EITHER a Status value of "A" or "B" but worked just fine when I added the whole formula to itself with just the change of the Status value. Thanks everyone for your help and kind suggestions. I have something I can work with though it still presents some barriers. I'm big on named cells, named fields and named records so nothing gets broken in my formulas if I delete a record or column/field. Multiple variables reporting would have been much simpler in Access but client insisting on Excel and I have worked with it since DAY 1 L but always more to learn. What fun. Dennis- Hide quoted text - - Show quoted text - Thanks for the link to Bob Phillips excellent discussion document on SUMPRODUCT. More powerful than I imagined. Does XL 2007 allow named column ranges in SUMIF or SUMPRODUCT? If it does it would sure save formula errors when a range is fixed by column reference and a new column is subsequently added to a referenced SS such as I need to use in 2003. Dennis |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
Does XL 2007 allow named column ranges in SUMIF or SUMPRODUCT?
Yes it does. But with 1 million rows, maybe not a good idea from a speed viewpoint. Dynamic ranges can be made dynamic in terms of row and column, so I don't see what your problem is. -- Regards Roger Govier "DbMstr" wrote in message oups.com... On Jun 26, 11:53 pm, "Roger Govier" wrote: However it too will NOT accept a named column such as "MonthClose" No, you cannot use whole columns as arguments in Sumproduct, other than in XL2007. You can use A1:A65535, or A2:A65536 i.e. just one short of the whole column. You have been given a method for creating a Dynamic range, which will grow to accommodate your range of data. The SUMPRODUCT did not support multiple choices for a single column range, for example summing for EITHER a Status value of "A" or "B" Yes it does. Try =SUMPRODUCT(--(A1:A10={"a","b"}),B1:B10) To understand more about Sumproduct, read Bob Phillips discussion documenthttp://xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "DbMstr" wrote in message ups.com... On Jun 26, 6:26 pm, driller wrote: thats a good one for naming dynamic ranges..with the sumproduct below... =SUMPRODUCT((MonthClose=RptMonth)*(Status="A")*(Rp tYear ="2007")*WeightedNet) from OP original post =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Instead this sums ALL records INSTEAD of just the selectedrecords. im confused and wonder how OP obtain a sort of ALL result with "2007" in the sum(if when RptYear is DONE as numeric from INT function.. regards, driller -- ***** birds of the same feather flock together.. "Roger Govier" wrote: Hi There needs to be a double unary minus in front of each expression, not a single minus. The double minus -- coerces the results of the expression for True to 1 and False to 0. alternatively, you could use =SUMPRODUCT((MonthClose=RptMonth)*(Status="A")*(Rp tYear ="2007")*WeightedNet) as far as your Dynamic Ranges are concerned, use the same column for the Count of rows, to ensure they are of equal length. Choose the column that you know will be populated for each row, e.g. Monthclose Refers to=OFFSET($A$1,0,0,COUNTA($A:$A)) Status Refers to=OFFSET($B$1,0,0,COUNTA($A:$A)) ReptYR Refers to=OFFSET($C$1,0,0,COUNTA($A:$A)) -- Regards Roger Govier "DbMstr" wrote in message oups.com... On Jun 26, 3:24 pm, driller wrote: based on the original sumif formula, w/o CSE , where the first result is a totalsum, assuming TOTAL SUM <0, this could also be with something like this... =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = "2007"),(WeightedNet)) regards, driller -- ***** birds of the same feather flock together.. "Barb Reinhardt" wrote: I'd use SUMPRODUCT for this =SUMPRODUCT(--(MonthClose=RptMonth),--(Status="A"),--(RptYear = 2007),(WeightedNet)) This assumes all arrays are the same length. HTH, Barb Reinhardt MonthClose=RptMonth)*(Status="A")*(RptYear="2007") ,WeightedNet)) "DbMstr" wrote: I don't know if this should be done in VBA or a nested IF formula. I need to produce Monthly and Quarterly totals based on records in a master SS with named columns/fields. I have a second SS also with named columns/fields where all calculations are performed and it is that SS that I use to obtain the results for my report SS. I found that SUMIF wanted an array range which DID NOT not allow full column selection. The number of records varies so a fixed range is not very useful. I have the following formula that works (well kind of) but instead of producing a total for the SELECTED records it results in a total of ALL records. Would like some feed back as to whether this seemingly simple task can be done with a formula or whether I should work on it with code? =SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYea r="2007"),WeightedNet)) Should SUM just those records selected for a given month number in the named field "RptMonth" (5) matches the value in "MonthClose" AND ALSO has the "Status" field value of "A" that have a "RptYear" value of "2007" and then sums the values in the "WeightedNet" field for just those records. Instead this sums ALL records INSTEAD of just the selectedrecords. Thanks in advance for your suggestions, Dennis- Hide quoted text - - Show quoted text - I had tried the SUMPRODUCT but apparently not correctly. This seems to work if I restrict the range to a fixed range K2:K89, rather than using named columns such as MonthClose I didn't need to quote the year since I created it using INT((YEAR(MonthYYClose) It doesn't work if I take out the "-" sign in front of each but I can certainly *-1 to get a positive result =SUMPRODUCT(-(K2:K89=5),-(J2:J89="A"),-(L2:L89 = 2007),(I2:I89))*-1 So now I have to figure out how to define a range that will vary depending upon the number of records in the SS. This test has 89 records but I will have a few thouand when completed. I will need a range that starts for example at K2: and stops at the last record, for example K2140???? Thanks for the suggestions, any more are greatly appreciated, Dennis- Hide quoted text - - Show quoted text - It's the SUMIF that returns ALL records IF you haven't made sure that the number of records are the same. For example=SUM(IF((K2:K89=5),I2:I90)) NOTE that the I90 ending is different than the K89. Even if simplified to one criteria. If entered as an array formula then {=SUM(IF((K2:K89=5),I2:I89))} equals the correct test total. $12,000 If NOT an array then =SUM(IF((K2:K89=5),I2:I89)) equals the total of ALL records. $450,000 Right, thanks people, we need the double "--" in front of the SUMPRODUCT ranges. If single "-" then need to multiply *-1 to make positive. The SUMPRODUCT seems to be a bit more versitile as it does not need to be an array formula to function. However it too will NOT accept a named column such as "MonthClose" in place of a limited range such as $K$2:$K$10000. Both will accept a named cell for a variable. So have to redesign IF I don't make the range big enough to handle the recordset which can grow over time. I felt sure that the beauty of using the named range in the formula would allow the SUMIF, SUM(IF( or the SUMPRODUCT formulas to accept any number of records up to 65K in 2003. Apparently NOT. The SUMPRODUCT did not support multiple choices for a single column range, for example summing for EITHER a Status value of "A" or "B" but worked just fine when I added the whole formula to itself with just the change of the Status value. Thanks everyone for your help and kind suggestions. I have something I can work with though it still presents some barriers. I'm big on named cells, named fields and named records so nothing gets broken in my formulas if I delete a record or column/field. Multiple variables reporting would have been much simpler in Access but client insisting on Excel and I have worked with it since DAY 1 L but always more to learn. What fun. Dennis- Hide quoted text - - Show quoted text - Thanks for the link to Bob Phillips excellent discussion document on SUMPRODUCT. More powerful than I imagined. Does XL 2007 allow named column ranges in SUMIF or SUMPRODUCT? If it does it would sure save formula errors when a range is fixed by column reference and a new column is subsequently added to a referenced SS such as I need to use in 2003. Dennis |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
Does XL 2007 allow named column ranges in SUMIF or SUMPRODUCT?
Yes it does. But with 1 million rows, maybe not a good idea from a speed viewpoint. Dynamic ranges can be made dynamic in terms of row and column, so I don't see what your problem is. Regards Roger Govier Welllll it appears that when a formula in 2003 references a fixed range on another sheet such as: =SUMPRODUCT((Prospects!$F$2:$F$10000="Y")*(MONTH(P rospects!$H$2:$H $10000)=3)*(Prospects!$C$2:$C$10000="W")) that when any records are deleted on the referenced SS named "Prospects" that the result is the formulas 10,000 end row is reduced by the number of records deleted. Soon the formulas could point to nothing????? I had previously thought that I could use a Named column so this issue would not exist. Unfortunately SUMPRODUCT does not allow a named column as it won't accept a complete column. I now have everything working except one formula but that is another issue. SUMPRODUCT has turned out to be the solution for most problems. Thanks again for all your help. Dennis |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
=SUMPRODUCT((Prospects!$F$2:$F$10000="Y")*(MONTH(P rospects!$H$2:$H
$10000)=3)*(Prospects!$C$2:$C$10000="W")) that when any records are deleted on the referenced SS named "Prospects" that the result is the formulas 10,000 end row is reduced by the number of records deleted. Soon the formulas could point to nothing????? If you set up Named ranges, InsertName Define Fcol Refers to =Prospects!$F$2:INDEX(Prospects!$F:$F,COUNTA(Prosp ects!$F:$F)) Repeat for Hcol and Ccol, but keep the CountA part of the formula always referring to Prospects!$F:$F as this will ensure that the ranges are always of the same dimension. The formula then becomes =SUMPRODUCT((Fcol="Y")*(MONTH(Hcol)=3)*(Ccol="W")) The size of each "column" will shrink and grow as data is appended or deleted from the source data but it will always represent the number of rows that are contiguously filled in what you decide is the "main" column i.e the one which will have no blanks in its data. -- Regards Roger Govier "DbMstr" wrote in message oups.com... Does XL 2007 allow named column ranges in SUMIF or SUMPRODUCT? Yes it does. But with 1 million rows, maybe not a good idea from a speed viewpoint. Dynamic ranges can be made dynamic in terms of row and column, so I don't see what your problem is. Regards Roger Govier Welllll it appears that when a formula in 2003 references a fixed range on another sheet such as: =SUMPRODUCT((Prospects!$F$2:$F$10000="Y")*(MONTH(P rospects!$H$2:$H $10000)=3)*(Prospects!$C$2:$C$10000="W")) that when any records are deleted on the referenced SS named "Prospects" that the result is the formulas 10,000 end row is reduced by the number of records deleted. Soon the formulas could point to nothing????? I had previously thought that I could use a Named column so this issue would not exist. Unfortunately SUMPRODUCT does not allow a named column as it won't accept a complete column. I now have everything working except one formula but that is another issue. SUMPRODUCT has turned out to be the solution for most problems. Thanks again for all your help. Dennis |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
On Jun 29, 1:55 am, "Roger Govier"
wrote: =SUMPRODUCT((Prospects!$F$2:$F$10000="Y")*(MONTH(P rospects!$H$2:$H $10000)=3)*(Prospects!$C$2:$C$10000="W")) that when any records are deleted on the referenced SS named "Prospects" that the result is the formulas 10,000 end row is reduced by the number of records deleted. Soon the formulas could point to nothing????? If you set up Named ranges, InsertName Define Fcol Refers to =Prospects!$F$2:INDEX(Prospects!$F:$F,COUNTA(Prosp ects!$F:$F)) Repeat for Hcol and Ccol, but keep the CountA part of the formula always referring to Prospects!$F:$F as this will ensure that the ranges are always of the same dimension. The formula then becomes =SUMPRODUCT((Fcol="Y")*(MONTH(Hcol)=3)*(Ccol="W")) The size of each "column" will shrink and grow as data is appended or deleted from the source data but it will always represent the number of rows that are contiguously filled in what you decide is the "main" column i.e the one which will have no blanks in its data. -- Regards Roger Govier "DbMstr" wrote in message oups.com... OK, perfect. This helps resolve my original issue of wanting to use a named column in my formulas. Creating and using a named range for say, Column F as a variable with =SheetName!$F$2:INDEX(Sheetname!$F:$F,COUNTA(Sheet name!$F:$F)) does get around the kludge of naming a fixed range such as $F$2:$F$65000 since SUMPRODUCT won't take a full column and counting the records speeds up the calculation since it is now only using a fixed number of records to search. However, the named columns used in a formula MUST ALL have the same number of records and it appears that all the records/fields need a value. Not sure how to use it if only one column in a formula is fully populated and another contains sum NULLs. Basic database structure should require a value in all fields and all records even if it is "Unk" or "0", so calculation and sorting errors don't occur anyway. I have noted that when debugging a formula using this type of named range (INDEX/COUNTA) based on a variable number of records that the GOTO command does NOT display this type of named range apparently since it is WELL, variable in size. It does show if you choose INSERT/ NAME/DEFINE and find it in the list. Could cause some confusion during a debugging trace. I usually have a "Formulas" sheet where I INSERT/PASTE/PAST LIST to reveal all formulas used in my workbook. The named range and its reference formula do show in this list. Using SUMPRODUCT formulat coupled with the INDEX COUNTA named ranges provides the powerful tools we need to create visually meaningful formulas that calculate more efficiently. It also helps limit the number of errors caused by missing or bad data since the formula won't work if you have any. Very cool. Thanks again for everyone's help and interest. I think this dialoge will be useful to a number of members. Dennis |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Total report based on 2 or 3 criteria. VBA or Formula
HI
However, the named columns used in a formula MUST ALL have the same number of records and it appears that all the records/fields need a value. That is why I said in my previous post keep the CountA part of the formula always referring to Prospects!$F:$F as this will ensure that the ranges are always of the same dimension. Use the column which will always be populated as the source of the CountA for all the named ranges, then all will be of equal dimensions. Fcol=Prospects!$F$2:INDEX(Prospects!$F:$F,COUNTA(P rospects!$F:$F)) Hcol=Prospects!$H$2:INDEX(Prospects!$F:$F,COUNTA(P rospects!$F:$F)) Ccol=Prospects!$C$2:INDEX(Prospects!$F:$F,COUNTA(P rospects!$F:$F)) -- Regards Roger Govier "DbMstr" wrote in message oups.com... On Jun 29, 1:55 am, "Roger Govier" wrote: =SUMPRODUCT((Prospects!$F$2:$F$10000="Y")*(MONTH(P rospects!$H$2:$H $10000)=3)*(Prospects!$C$2:$C$10000="W")) that when any records are deleted on the referenced SS named "Prospects" that the result is the formulas 10,000 end row is reduced by the number of records deleted. Soon the formulas could point to nothing????? If you set up Named ranges, InsertName Define Fcol Refers to =Prospects!$F$2:INDEX(Prospects!$F:$F,COUNTA(Prosp ects!$F:$F)) Repeat for Hcol and Ccol, but keep the CountA part of the formula always referring to Prospects!$F:$F as this will ensure that the ranges are always of the same dimension. The formula then becomes =SUMPRODUCT((Fcol="Y")*(MONTH(Hcol)=3)*(Ccol="W")) The size of each "column" will shrink and grow as data is appended or deleted from the source data but it will always represent the number of rows that are contiguously filled in what you decide is the "main" column i.e the one which will have no blanks in its data. -- Regards Roger Govier "DbMstr" wrote in message oups.com... OK, perfect. This helps resolve my original issue of wanting to use a named column in my formulas. Creating and using a named range for say, Column F as a variable with =SheetName!$F$2:INDEX(Sheetname!$F:$F,COUNTA(Sheet name!$F:$F)) does get around the kludge of naming a fixed range such as $F$2:$F$65000 since SUMPRODUCT won't take a full column and counting the records speeds up the calculation since it is now only using a fixed number of records to search. However, the named columns used in a formula MUST ALL have the same number of records and it appears that all the records/fields need a value. Not sure how to use it if only one column in a formula is fully populated and another contains sum NULLs. Basic database structure should require a value in all fields and all records even if it is "Unk" or "0", so calculation and sorting errors don't occur anyway. I have noted that when debugging a formula using this type of named range (INDEX/COUNTA) based on a variable number of records that the GOTO command does NOT display this type of named range apparently since it is WELL, variable in size. It does show if you choose INSERT/ NAME/DEFINE and find it in the list. Could cause some confusion during a debugging trace. I usually have a "Formulas" sheet where I INSERT/PASTE/PAST LIST to reveal all formulas used in my workbook. The named range and its reference formula do show in this list. Using SUMPRODUCT formulat coupled with the INDEX COUNTA named ranges provides the powerful tools we need to create visually meaningful formulas that calculate more efficiently. It also helps limit the number of errors caused by missing or bad data since the formula won't work if you have any. Very cool. Thanks again for everyone's help and interest. I think this dialoge will be useful to a number of members. Dennis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print report based on criteria | Excel Discussion (Misc queries) | |||
Lookup Value (and Total) Based on Two Criteria | Excel Worksheet Functions | |||
Monthly Report | Excel Discussion (Misc queries) | |||
get a total based on criteria in two columns | Excel Worksheet Functions | |||
Formula help in a monthly sales report. | Excel Worksheet Functions |