+ Reply to Thread
Results 1 to 3 of 3

Formula to sum a range of cells if some critera is in another cell

  1. #1
    Registered User
    Join Date
    11-11-2008
    Location
    Wilmington
    Posts
    26

    Formula to sum a range of cells if some critera is in another cell

    So I have a formula that is working:

    =SUMPRODUCT(--(ISNUMBER(FIND(E1,B1:B100))),N1:N100)

    This Looks in column B for the text in cell E1, then sums all the matches of column N

    To make it simple

    Column B Column N Cell E1 = 868
    86800 50.00
    86812 150.00
    86813 12.00
    86800 20.00
    87820 30.00
    87823 15.75

    The forumla will result in the summing of N that has column B of "868" anywhere in the CEll.


    WHAT I NEED IS THIS:
    to sum based on an ARRAY of criteria, not just what's in E1.
    Need E1, or e2, or e3.... ?

    Can anyone help?

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formula to sum a range of cells if some critera is in another cell

    If the numbers in column B are always 5 digits..

    Try
    =SUMPRODUCT(--(INT(B1:B100/100)=E1),N1:N100)

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,447

    Re: Formula to sum a range of cells if some critera is in another cell

    What happens if different criteria are present in the same cell ( contains E1 and E2) ?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1