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

[ssGuru]

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]

what about SUBTOTAL function? Give it a try. the first argument in your case
should be 9 (look at excel help for an explanation).