Monthly Total report based on 2 or 3 criteria. VBA or Formula

[DbMstr]

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")*(RptYear="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

 

[Guest]

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))

 

[Guest]

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

 

[Guest]

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

 

[DbMstr]

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..









- 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

 

[Ragdyer]

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.

 

[Roger Govier]

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")*(RptYear
="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))

 

[Guest]

thats a good one for naming dynamic ranges..with the sumproduct below...

=SUMPRODUCT((MonthClose=RptMonth)*(Status="A")*(RptYear ="2007")*WeightedNet)

from OP original post
=SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYear="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

 

[DbMstr]

thats a good one for naming dynamic ranges..with the sumproduct below...

=SUMPRODUCT((MonthClose=RptMonth)*(Status="A")*(RptYear ="2007")*WeightedNet)

from OP original post
=SUM(IF((MonthClose=RptMonth)*(Status="A")*(RptYear="2007"),WeightedNet))


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..









- 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

 

[Roger Govier]

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

 

[DbMstr]

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.


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












L> but always more to learn. What fun.


- 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

 

[Roger Govier]

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.

 

[DbMstr]

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(Prospects!$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

 

[Roger Govier]

=SUMPRODUCT((Prospects!$F$2:$F$10000="Y")*(MONTH(Prospects!$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, Insert>Name>
Define Fcol
Refers to
=Prospects!$F$2:INDEX(Prospects!$F:$F,COUNTA(Prospects!$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.

 

[DbMstr]

If you set up Named ranges, Insert>Name>
Define Fcol
Refers to
=Prospects!$F$2:INDEX(Prospects!$F:$F,COUNTA(Prospects!$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


news:[email protected]...

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(Sheetname!$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
(e-mail address removed)

 

[Roger Govier]

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(Prospects!$F:$F))
Hcol=Prospects!$H$2:INDEX(Prospects!$F:$F,COUNTA(Prospects!$F:$F))
Ccol=Prospects!$C$2:INDEX(Prospects!$F:$F,COUNTA(Prospects!$F:$F))