Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2008
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Calc help needed (1.0)

    Can you help me derive the total for Column AI (Scheduled March) + Column AV (Estimated Effort) if Meg Month (Column O) = Mar or if In Progress stage (Column P) = Build, Deploy, Warranty, Obtain Authorization. The total should be place in Column AW (MEG MARCH). I have attached a sample to help. Any help you can offer is greatly appreciated.

    I reall need a formula for deriving the total.
    Attached Files Attached Files

  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: Excel Calc help needed (1.0)

    Could you attach a version that is not saved in XL2007. My computer can not read XL2007 files

    Steve

  3. #3
    New Lounger
    Join Date
    Mar 2008
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Calc help needed (1.0)

    No problem Steve. Reposted in .xls format
    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: Excel Calc help needed (1.0)

    The array formula (confirm with ctrl-shift-enter) will give the sum of columns AI and AV when th value in O = "Mar" or the value in P is in the list.

    =SUM(IF(($O$2:$O$10="Mar")+ISNUMBER(MATCH($P$2:$P$ 10,{"Build","Deploy","Warranty","Obtain Authorization"},0)),$AI$2:$AI$10+$AV$2:$AV$10))

    Since this is XL2007, you may be able to use the new SUMIFS formula as well...

    Steve

  5. #5
    New Lounger
    Join Date
    Mar 2008
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Calc help needed (1.0)

    Additionaly how can I rework the formula to update the AW column with column AV total when the status has a value of "approved" in column (N) and Column (O) has a value of March. The second condition that needs to be placed in the formula will update AW with the total of Sheduled March (AI) if no totals exist in column AV and column N has a value other than Approved but not blank. I also would like the counts in AW to be updated whenever modifications are made to AI or AV. The last formula did not adjust the counts when I made changes to the fields totals. Is thier anyway to make this happen??

  6. #6
    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: Excel Calc help needed (1.0)

    I don't understand what you want the sum to do if "Approved" or some of your other conditions.

    If Approved and Mar than it will still be included in the total since you asked for Mar OR something from that list. Anything with Mar will be added no matter what is in the other list.

    The formula should update (do you have calculations on automatic?). the formula is a SUMMATION of all the cells matching the criteria. Are you after something that is more a "row based" subtotal (creating a column of intermediate values) and then sum them at the end? This formula does not do that.

    Perhaps you should indicate what values you want in the various cells and explain the logic of the calculations and include examples of the various "types"/conditions.

    Steve

Posting Permissions

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