Forum Discussion

Enrique García Peña's avatar
Enrique García Peña
Copper Contributor
Apr 10, 2018

RE: returning multiple results from a single criteria

Hello, someone can help me please. I want to use a rank in a single criteria, for example: I have a list of years since 1900 to 2018 and I need to take the values from 1990 to 1999 in SUMAR.SI(A2:A39,"1990:1999",B2:B39)
Formulas & Functions

8 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Apr 11, 2018

    Hi Enrique,

     

    You recently replied to this conversation, and you asked me a similar question!
    Anyway, I've given you a fairly good solution.

     

    With regards to this question, you can depend on this algorithm:

    • Extract the left-most three numbers from each year in a new column by using this formula:
    =MID(A2,1,3)
    • In this new column, we have unified every ten years (decade) in a unique number.
    • We can use SUMIF function with a single criterion depends on this new column as follows:
    =SUMIF(B2:B120,"199",C2:C39)
    • Now we have the sum of the values in the decade (1990 - 1999). 

     

    With this new column, instead of this wrong syntax:

    =SUMAR.SI(A2:A39,"1990:1999",B2:B39)

    Use this:

    =SUMAR.SI(A2:A39,"199",B2:B39)

     

    Please find the attached file to see that.

     

    I hope this helps you

    Haytham

  • Enrique García Peña's avatar
    Enrique García Peña
    Copper Contributor
    Apr 10, 2018

    Actually I want to do this SUMIFS(B2:B38,A2:A38,">1990",A2:A38,"<1999") but using a single criteria in SUMIF(A2:A38,"single criteria 1990:1999",B2:B38)

    • Wyn Hopkins's avatar
      Wyn Hopkins
      MVP
      Apr 10, 2018

      Hi Enrique García Peña

       

       

      You've accidentally tagged your question onto someone else's question. Could you remove your questions from here and post them via Start New Conversation.