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