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