Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Sumif Problem

  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Sumif Problem

    I have a spreasheet containing account numbers in Sheet1 and account numbers and values on sheet "imported data"

    I have tried to use the SUMIFformula, but it returns zero. The reason for using the sumif formula is that I the same account number contains different values as it is loaded in the Trial Balance with different descriptions


    The reason where it may return zero is the the account numbers appears in Sheet 1 are numbers and those on sheet "imported data" are text


    I would like a formula on sheet Col C to add the values on sheet "imported data" based on the account number on sheet1

    Your assistance in resolving this is most appreciated
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Try filling this down in Sheet1

    =IF(A1="",0,SUMIF('Imported Data'!$A1:$A25,C1,'Imported Data'!E1:E25))

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Kevin

    Thanks for the help. Can't believe that I messed this formula up. I've used SumIF many times and is very easy.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    Re: "The reason where it may return zero is the the account numbers appears in Sheet 1 are numbers and those on sheet "imported data" are text"

    If the source data in column [A] is a 'Text account number', but the thing you are using to match (in column [C] ) is a 'numeric account number', then you can convert your lookup numeric value to text like this:
    =IF($A1="",0,SUMIF('Imported Data'!$A:$A,""&$C1,'Imported Data'!E:E))

    If it had been the other way around, you can use..
    =IF($A1="",0,SUMIF('Imported Data'!$A:$A,$C1+0,'Imported Data'!E:E))

    zeddy

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the reply. I think we are getting closer. I have established that the numbers are text on both sheets for eg in Col C sheet1 account number '1100 and on sheet imported data '1100. The sumif as is stands gives me zero. If we can resolve this issue then it will work

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Attached please find sample data which will assist you in resolving the problem

    Your assistance in this regard is most appreciated



    Regards


    Howard
    Attached Files Attached Files

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    Had a quick look at your attached sample file.
    I don't see any sumif formulas?
    Is it the right file??

    zeddy

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the reply. Please have a look at sheet1 G4-This contains a sumif formula. It returns zero, where is should return 432 (value of account 1100 on on sheet imported data)

    Hope this helps

    Regards

    Howard

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    I have attached the file again -see SUMIF formula in G4 on sheet1. Your assistance in resolving this is most appreciated

    Regards

    Howard
    Attached Files Attached Files

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    Just got back.
    Had a look at your file again.
    I also had some issues with using SUMIF to get your results.
    So I went back to using array formulas which worked.
    See attached file.

    Meanwhile, I will look again to find out what is causing the SUMIF problems.
    However, I have a 300mile trip tomorrow so may take a little longer.

    zeddy
    Attached Files Attached Files

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    An interesting problem!
    So, in the attached file, I have your sheet1 (from your file) where the SUMIF formulas do not work, as you can see in columns [G] and [I] on sheet named [Sheet1] where they all show zero values.
    Now here's the interesting thing:
    1. insert a new sheet, then go back to [Sheet1], copy the entire sheet (put cellpointer in cell [A1], then press Ctrl-A, then press Ctrl-C to copy entire sheet)
    2. Go back to your new sheet, press Ctrl-V to paste the copied data.

    You now have a copy of the sheet1, but this time the SUMIF formulas in columns [G] and [I] are now working!

    Go figure!!
    I don't know how you got your sheet1, but something is a bit iffy with it!!!

    zeddy
    Attached Files Attached Files

  12. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2013-04-03)

  13. #12
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    @ HowardC

    Try cleaning your file up for starters!!

  14. The Following User Says Thank You to Kevin@Radstock For This Useful Post:

    HowardC (2013-04-03)

  15. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy & Kevin

    Thanks for your valuable input, this is much appreciated. Much have been some gremlin's on sheet1. The workbook is a file that was converted into Excel from Quattro Pro many years ago so I guess it may have caused some problems

    Zeddy, hope you have a good trip

    Regards

    Howard

  16. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    Thanks for the explanation. Quattro Pro??? Surely you are too young for that.

    zeddy

  17. #15
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Quattro Pro was a program that I inherited from the person I took over from 10 years ago. I hated it and switched over to Excel within a few months of joining the company. Believe it or not or Finance Director still useds Quattro and refuses to change

Page 1 of 2 12 LastLast

Posting Permissions

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