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
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 |
#4
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 |
#5
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 |
#6
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 |
#7
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 |
#8
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 |
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 |