Results 1 to 3 of 3

Thread: SUMIFS

  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Loungers, I'm having problems with the SUMIFS formula - see attached. As far as I can see I've see the range and criteria (see BW19 & BZ19) but it is not retuning a value.

    Also, I understand that SUMIFS will not work on earlier versions on excel, so wondering if anyone can suggest any alternative formulas that would return the required value and work on all versions.

    Any thoughts?
    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
    Since you never have a column with an "A" and a column with an "Hrs" It is not surprising that you are not getting anything but 0. It only sums those columns that have both...

    A formula not using the SUMIFS is an array formula (confirm with ctrl-shift-enter in BW9:
    =SUM(IF((C4:BS4="a")*(F8:BV8="Hrs")*ISNUMBER(F9:BV 9),F9:BV9))

    And in BX9:
    =SUM(IF((C4:BS4="b")*(F8:BV8="Hrs")*ISNUMBER(F9:BV 9),F9:BV9))

    These also account for the "a" and "hrs" being offset...

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Steve, Yes - I understand, you are right no wonder.

    Thanks for the alternative formulas - will plug them in and see how if goes


    Regards

    Dean

Posting Permissions

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