Results 1 to 4 of 4

Thread: Sum with criteria only numbers in range that contains text

  1. #1
    Seeker samdk's Avatar
    Join Date
    Dec 2018
    Location
    Paris
    Posts
    11
    Articles
    0
    Excel Version
    2013

    Unhappy Sum with criteria only numbers in range that contains text



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

    Hello,

    I have this specific range with numbers and " " (which means that the cell is blank, but contains space -> isn't a number) :

    1 A B C D E F G H I J
    2 Criteria column jan fév mar apr mai juin july aug sept
    3 Name 1 " " " " 5 12 17 22 25 21 23
    4 Name 1 " " " " 3 7 5 14 19 20 18
    5 Name 2 " " " " " " " " " " 9 12 16 22
    6 Name 2 " " " " " " " " " " 3 4 6 8
    7 Name 3 " " " " " " 0 3 5 7 8 9
    8 Name 3 " " " " " " 0 0 1 3 5 8
    9 Name 4 " " " " " " " " " " " " 12 24 30
    10 Name 4 " " " " " " " " " " " " 9 16 27


    Then I need to sum numbers (in cumuluative way) according to :

    Criteria 1 : Name 2
    Criteria 2 : august
    the result will be : 9+12+16+3+4+6 =50

    Criteria 1 : Name 4
    Criteria 2 : sept
    the result will be : 12+24+30+9+16+27 =118

    Thanks in advance for your help.

    Cheers,
    Sam

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,726
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    Try this:

    =SUMPRODUCT(($A$2:$A$9=M2)*(COLUMN($B$1:$J$1)-1<=MATCH(M3,$B$1:$J$1,0)),$B$2:$J$9)
    Attached Files Attached Files
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Seeker samdk's Avatar
    Join Date
    Dec 2018
    Location
    Paris
    Posts
    11
    Articles
    0
    Excel Version
    2013
    Thanks a lot AliGW for your time,

    Your response is much appreciated, it works like a charm !

    Have a perfect day !

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,726
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    You're welcome.
    Ali
    Enthusiastic self-taught user of MS Excel!

Tags for this Thread

Posting Permissions

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