Prev: Excel 2007 - Crash
Next: office 2007 and vsto
From: ssGuru on 26 Jun 2007 17:40
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

From: Andrea on 26 Jun 2007 21:16
what about SUBTOTAL function? Give it a try. the first argument in your case
should be 9 (look at excel help for an explanation).

"ssGuru" 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")*(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
>
>
 | 
Pages: 1
Prev: Excel 2007 - Crash
Next: office 2007 and vsto