Results 1 to 4 of 4

Thread: Offset & Match Formula + Sum Formula

  1. #1

    Offset & Match Formula + Sum Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    In column D (of the Results tab) I am trying to sum the values of the first 3 columns only when the first 3 columns are not empty. As you will note, the first 3 columns in this tab (Results) have Offset/Match formulas.

    The point of the Results tab is that when i export the data into the Raw tab, the column order can possibly change. Thus, i use the offset/match function in the Results tab to find information for a specific column regardless of where it is located in the Raw tab.

    Can someone tell me why my formula of =IF(AND(A2="",B2="",C2=""),"",A2+B2+C2) gives me a #VALUE whenever Column A, B, C (in Results tab), have a value? It should simply be adding the columns and giving me a total!

    Thanks for your help!
    Attached Files Attached Files

  2. #2
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    118
    Articles
    0
    The "" results are text strings, and Excel will not add them to a number. Give this a try:

    Code:
    =IF(AND(A2="",B2="",C2=""),"",SUM(A2:C2))
    or,

    Code:
    =IF(AND(A2="",B2="",C2=""),"",SUM(A2,B2,C2))
    The SUM function will ignore text.

    Cheers,

  3. #3
    Thank you for this. Should I change my OffSet/Match function so number in the result tab comes in as numbers? If so, how should I change it?

  4. #4
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    118
    Articles
    0
    It appears that you've already set the cells to display a percentage (if one is provided), so if my solution is working for you, then you probably don't need to make any further changes. I suppose it would depend on what you ultimately intend to do from this point forward.

    Cheers,

Posting Permissions

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