Results 1 to 9 of 9

20021005, 15:17 #1
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Calculating Formulas in SUMIF/COUNTIF? (Excel 97SR2)
Hi Folks
First post.... Searched message board without success, which I appreciate doesn't mean my question isn't already answered there..
Ok, What I'm trying to do is to do away with an intermediate worksheet that calculates financial values over a 104 week period. Elsewhere I summarise these values based upon financial quarters and/or a specific criteria entered. I'm only interested in the summary, I have no real use for the individual 104 week calculations.
Each of the 104 weekly calculations are calculated thus:
=IF(ISNUMBER(PlanView!D5),((PlanView!D5*(IF(Includ eDailyCost,Planning!$DF5,0)+IF(IncludeTandS,Planni ng!$DG5,0)))*5)*D$4,"")
There are 40 rows. (Worse, there are two worksheets with similar calculations.)
Elsewhere I will summarise each row something like:
=IF(PlanViewCost!B5="SelectedValue", SUM(D5D5),0)
What I 'think' I ought to be able to do is perform the first calculation above inside the SUM(D5D5) in the second calculation.
Is this possible?
Regards
Peter

20021005, 17:48 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Calculating Formulas in SUMIF/COUNTIF? (Excel 97SR2)
You should be able to do it with ARRAY formulas, relatively simply
. I am a little confused about exactly what formulas you want to get rid of/combine.
What sheet is (sumD5D5) from?
I assume tha IncludeDailyCost and IncludeTandS are range names that contain true or False.
What is "SelectedValue" it does not seem to be a range name (since it is in quotes), I was thinking it might be a generic reference to a value, but then why not a cell name or range name?
Could you post an example spreadsheet with proprietary info deleted? I was trying to set something up, but I got confused on some of the finer details.
You could note what the "temp column calcs" are that you don't need and what sums of these you want to keep.
Steve

20021006, 10:40 #3
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Formulas in SUMIF/COUNTIF? (Excel 97SR2)
Hi Steve
OK.. I've attached a stripped down version of two of the worksheets from the model that I am working on, with comments. Hopefully this should make my intentions somewhat clearer. Any help gretly appreciated.
Regards
Peter

20021006, 12:03 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Calculating Formulas in SUMIF/COUNTIF? (Excel 97SR2)
If I understand correctly:
Formula D21 could be replaced with(all one line, confirm w/ ctrlshift enter NOT enter):
=SUMPRODUCT((PlanView!D5:P5),$D$4:$P$4,IF(D$3:P$3> =$D$20,1,0))*(IncludeDailyCost*$S5+IncludeTandS*$T 5)*5
This can be copied down d22, d23, etc.
This is the sum >= the date in D20.
If you want the sum >=D20 and <E20 use thisall one line, confirm w/ ctrlshift enter NOT enter):
=(SUMPRODUCT((PlanView!D5:P5),$D$4:$P$4,IF(D$3:P$3 >=$D$20,1,0))SUMPRODUCT((PlanView!D5:P5),$D$4:$P$4,IF(D$3:P$3>$ E$20,1,0)))*(IncludeDailyCost*$S5+IncludeTandS*$T5 )*5
You already have the dates in row 3 of PlanView. If you add the values from row 3 into plan view, add the 2 "includes" names and the values in cols S&T into planview, and these formulas, all the data could be done in PlanView without any of the "intermediate calcs" on the current sheet and the current sheet could be eliminated.
Steve

20021006, 13:14 #5
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Formulas in SUMIF/COUNTIF? (Excel 97SR2)
Hi Steve
Many thanks... Now I'll try and understand why it works :)
As an aside... Array formulas are not something I make great use of, due largely to lack of understanding, can you point me at any definative resources for bringing myself up to speed with them?
Regards
Peter

20021006, 17:30 #6
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Formulas in SUMIF/COUNTIF? (Excel 97SR2)
Hi Steve
Thanks... The site reference is very useful AND the array calculations, with suitable modifications do exactly what I asked for.... Of course, that isn't what I really want to do.. My fault.. I've reattached the sample spreadheet in the hope that explains what I'm aiming at.
In essence what I need to be able to do is to summarise for ALL rows in the bigger array where the Group matches that entered/selected. i.e. What I end up with is a breakdown of costs for specific Groups by financial quarters. Am I making any sense?
Regards
Peter

20021006, 18:14 #7
 Join Date
 Oct 2002
 Location
 Wellington, Wellington, New Zealand
 Posts
 621
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Formulas in SUMIF/COUNTIF? (Excel 97SR2)
Peter
<img src=/S/hello.gif border=0 alt=hello width=25 height=29>I looked at your sheet, and hope I'm understanding it right.
<img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> I wondered why what you're doing is not much more simply achieved by a bit of restructuring and a Pivot table.
I run a spreadsheet with transactions and transaction dates (organised as a list). To make it produce reports similar to the ones you seem to be asking for I create a new column or two of simple of 'tag' fields (e.g. QUARTER, and LEDGER) which can be easily calculated off the source data.
The resulting structure can then easily be summarized as a Pivot Table. I'd assume that a data table a bit like that would work for you too.

20021006, 20:17 #8
 Join Date
 Oct 2002
 Location
 RossonWye, Herefordshire, United Kingdom
 Posts
 212
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Formulas in SUMIF/COUNTIF? (Excel 97SR2)
Hi Andrew
Thanks for your feedback. You may have a good point. There is a lovely Irish expression that I am very fond of, "Sure you don't want to be starting from here...", which as with many things Irish isn't daft at all, it's a polite means of saying "@#$!%, you're well lost mate". Hence, you may be right about the pivot table. I've started down one particular alleyway and the light may be getting a bit dim..
The model I am working on is a resource scheduling and costing model. The key part is getting user input for levels of resource per day. In the background I then calculate the cost of those resources. As with many of these things there is a 'Plan' and and an 'Actual'. What Steve has helped me greatly with is a means of bypassing the need to have to calculate and hold in a temporary sheet the cost per week, per resource line, since all I'm really interested is the Quarterly financial cost.
The cost calculation is related to a skills profile and is subject to a monthly inflation charge. I guess I could put all this into a table, I sort of had that anyway, but was actually trying to get rid of the detailed costs tables for Plan and Actual since other than allowing me to perform subtotaling per quarter/cost sector, they just take up a lot of space.
A bit like array formulas, I tend to avoid pivot tables, largely based upon my ignorance of them. Maybe I'll try getting my hands dirty at some time. Especially if this particular alley gets any dimmer!
Regards
Peter

20021006, 21:17 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Calculating Formulas in SUMIF/COUNTIF? (Excel 97SR2)
<P ID="edit" class=small>(Edited by WebGenii on 06Oct02 18:17. added Hyperlink)</P>Try this site by Chip Pearson:
http://www.cpearson.com/excel/array.htm