Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating Formulas in SUMIF/COUNTIF? (Excel 97-SR2)

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 97-SR2)

    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

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Formulas in SUMIF/COUNTIF? (Excel 97-SR2)

    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
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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 97-SR2)

    If I understand correctly:

    Formula D21 could be replaced with(all one line, confirm w/ ctrl-shift 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/ ctrl-shift 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

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Formulas in SUMIF/COUNTIF? (Excel 97-SR2)

    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

  6. #6
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Formulas in SUMIF/COUNTIF? (Excel 97-SR2)

    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 re-attached 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
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    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 97-SR2)

    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.

  8. #8
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Formulas in SUMIF/COUNTIF? (Excel 97-SR2)

    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 sub-totaling 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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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 97-SR2)

    <P ID="edit" class=small>(Edited by WebGenii on 06-Oct-02 18:17. added Hyperlink)</P>Try this site by Chip Pearson:
    http://www.cpearson.com/excel/array.htm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •