Formula summing up even if the reference cell is empty

  • Hi Folks

    I have this formula which looks at a cell, then looks for every instance of the name in that cell on another sheet and sums the figures in another column wherever that name occurs. However, if the reference cell is blank, for some reason it then sums everything in the column that contains figures. I don't understand why???


    Data_Sheet-YTD C:C contains all the names the name in Analysis-YTD K24 is looking for and Data_Sheet-YTD J:J contains the figures to be summed.

    I've tried using and IFERROR addition, no joy...

    Any thoughts please??

    Kind regards and many thanks..


    Edited once, last by Carim ().

  • Hello,

    When it comes to defining ranges, you should try to restrict to your work area C1:C400 ...

    Moreover, your criteria is defined with "*" and this wildcard character covers all possibilities ...

    Finally, just make sure Empty is indeed Empty and not Blank ...

    Please confirm

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi Carim

    Thank you for your quick reply, you really do come to my aid. The reason for the "*" is that The name in K24 is contained within a string the column C:C

    For example K24 contains the word Eastbourne and in C:C there could be multiple entries that contain that word, such as

    "1234 Eastbourne abcd" and

    "1234 Eastbourne CDEF" etc etc

    Currently it looks for that word Eastbourne then sums up whatever is in J:J

    I really hope I'm making sense here??? :)

    Kind regards


  • You haven't said what you actually want as the result if K24 is blank? Perhaps this:


    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!