Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Posts
    38

    Unanswered: SUMIF function returns no result

    Hi all, I am trying to use the SUMIF function but for some reason I do not get a result. If I copy an example into my worksheet the example works.



    ....A..........B......C......D.....E......F.....G. .....H......I.......J......K......L.......M....... N......O
    1..Breed.....1.....1a.....2.....2a.....3.....3a... ..4.....4a.....5.....5a.....10.....10a.....11....1 1a
    2..1austs.................................1
    3..1cav.............1.............1
    4..1cav.....................1
    5..1cav........................................... ........1
    6.. 1tib............................................1

    Above is a sample of my spreadsheet. (Had to put ...'s to make columns)
    My formula reads =SUMIF(A2:A6,"1cav",B2:O6)
    Result should be 4
    Result is 0 WHY?????
    Last edited by kim burke; 09-17-09 at 01:58.

  2. #2
    Join Date
    Jul 2007
    Posts
    54

    SUMIF only totals the first column

    At first glance this should work, but I think that SUMIF only totals one column, so you are getting the total of column B (which is zero!). I tried it on my Excel 2003 and that is what I get.

    Therefore one solution would be to add column P which is the sum of all the preceding columns (ie SUM(B2:O2) and so on). Then your SUMIF would become =SUMIF(A2:A6,"1cav",P2:P6).

    One thing to watch is if your column A entry contains "1cav " (ie the correct data but with a space before or after) then your SUMIF formula will not recognise it correctly. Likewise those 1 markers, if they are text and not numeric then the SUMIF won't count them.

    HTH
    Caspar

  3. #3
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72

    Forget SUMIF use SUMPRODUCT

    Hi

    The SUMPRODUCT formula to do this would be:

    =SUMPRODUCT(($A$2:$A$6="1cav")*($B$2:$O$6))

    With SUMPRODUCT you can add many columns, use many columns as criteria you can work with rows, you can count, sum, calculate average.

    SUMPRODUCT is the most powerful function in Excel but more than 90% of users ignore this.

    Even the Microsoft's developers ignore the potential of SUMPRODUCT. That is why they have spent Million$ to develop 4 new functions in 2007 that are totally useless (SUMIFS, COUNTIFS, AVERAGEIFS and AVERAGEIF). These new functions are useless because you can do what these functions do with a single function that has been in Excel since 1997 (SUMPRODUCT) and you can even do more with SUMPRODUCT.

    Visit Microsoft excel functions and formulas
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  4. #4
    Join Date
    Aug 2007
    Posts
    38
    Thanks CasparV - Using a separate totalled column works perfectly. The fact that SUMIF can only SUM one column is not mentioned anywhere. Thanks heaps.

  5. #5
    Join Date
    Aug 2007
    Posts
    38
    Hi Pierrevbaexcel - You are a marvel!!! SUMPRODUCT saves me putting in an extra column. I must look in to SUMPRODUCT and see what else I can use it for. Thanks heaps.

  6. #6
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72

    Sumproduct

    Kim

    SUMPRODUCT is the single function that I use the most to develop solutions for my clients (large and small enterprises).

    Without any macros or any programming I have developed fully automated reporting applications downstream from large central database, accounting programs, sales programs, manufacturing programs....

    I have developed permanent inventories and purchasing applications, production planning applications and the most simple and customizable accounting application that allows the user to develop reports by employee, supervisor, sub-project, types of work and any combination thereof.

    I have designed a special tutorial on SUMPRODUCT and you can download it from my website along with the applications mentionned above (20 in all).

    Visit http:\\Microsoft excel functions and formulas

    Macros and VBA are also great tools. Visit

    http:\\Free Help on VBA for Excel (Macros)
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

Posting Permissions

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