Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sumif overmultiple columns formula (2003)

    Is there any way to create a simple formula to sumif over multiple columns as follows:

    =SUMIF('5.5'!S:S,E3,'5.5'!$D:$M)

    The above formula sums over column D only subject to the criteria in E3. I need to sum over columns D to M, where M is the current month and needs to be updated each month so as to get the year to date figure.

  2. #2
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif overmultiple columns formula (2003)

    Hans

    Can you tell me what is the extra ,0 near the end of the formula is for?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sumif overmultiple columns formula (2003)

    I used an IF nested in the SUM. The IF function has three arguments: condition, result-if-True and result-if-False.

    IF('5:5'!S1:S65000=E3,'5.5'!D1:M65000,0)

    '5:5'!S1:S65000=E3 is the condition
    '5.5'!D1:M65000 is the result-if-True (these are the data to be added if the condition is true)
    0 is the result-if-False (don't add anything if the condition is false)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sumif overmultiple columns formula (2003)

    Try the following:

    =SUM(IF('5:5'!S1:S65000=E3,'5.5'!D1:M65000,0))

    as an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter. (Array formulas don't work wll with entire columns, so I used rows 1:65000)

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif overmultiple columns formula (2003)

    Hans

    I have tried the formula. Am I right in thinking that the array multiplication fails if one of the arrays has a cell in it containing text.

    In the end I created a sum formula in a spare column in sheet 5.5 and then used sumif('5.5'!S:S,E3,'5.5'!R:R) which seemed to eliminate this problem.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sumif overmultiple columns formula (2003)

    The array formula should still work if some of the cells contain text (there is no array multiplication involved, by the way.) But your solution is easy and will work well, so I'd use that.

  7. #7
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif overmultiple columns formula (2003)

    A non array formula, but still no whole column references,

    =SUMPRODUCT(('5.5'!S1:S65000=E3)*('5.5'!D1:M65000) )

Posting Permissions

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