Forum Discussion

Enrique García Peña's avatar
Enrique García Peña
Copper Contributor
Apr 11, 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
  • 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 11, 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)

Resources