+ Reply to Thread
Results 1 to 13 of 13

Add Values only if Matches with criteria

  1. #1
    Registered User
    Join Date
    06-05-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2007
    Posts
    29

    Question Add Values only if Matches with criteria

    Hi All,

    I want to add formula in G4 to add values from cell range D4:D24
    where Text of C4:C24 matches with G3 and
    Number of cell summed should be upto C1.(Means if c1 is 4 should add first 4 values only)
    Please see Image for explanation.

    tt.JPG
    Attached Images Attached Images
    Last edited by Ashurk77; 01-30-2014 at 08:16 AM.

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Add Values only if Matches with criteria

    =SUM(IF(OFFSET($B$4,0,,SMALL(IF($B$4:$B$24=G$3,ROW($C$4:$C$24)-ROW($B$4)+1),$C$1))=G$3,OFFSET($B$4,0,1,SMALL(IF($B$4:$B$24=G$3,ROW($C$4:$C$24)-ROW($B$4)+1),$C$1))))
    Confirm COntrl+Shift+enter
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Registered User
    Join Date
    06-05-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Add Values only if Matches with criteria

    Thanks
    Robert
    but it is giving wrong output,
    Before that I was using this

    =SUMIF(AI5:OFFSET($AI5,6*($K$2-1),,,),BJ4,$AK$5:$AK$700)
    where AI is Area list
    K2 is count number
    Bj4 is Area to be matched
    and AK contain Values

    But now I want to add Count condition as well.

  4. #4
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Add Values only if Matches with criteria

    Please see attached
    Not sure about the count.
    Coudl you elaborate?add.xlsx

  5. #5
    Registered User
    Join Date
    06-05-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Add Values only if Matches with criteria

    Yes it is working fine here but some how on my original data is is not working right.
    Can we do something on my above posted formula?

  6. #6
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Add Values only if Matches with criteria

    Attached your workign file.
    WHy my formual does not work?

  7. #7
    Registered User
    Join Date
    06-05-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Add Values only if Matches with criteria

    pLEASE SEE MY eXCEL
    Attached Files Attached Files
    Last edited by Ashurk77; 01-30-2014 at 12:17 PM.

  8. #8
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Add Values only if Matches with criteria

    This setup is very different from the one on your jpg.
    Could you specify your outcome and where the criteria cells are?

  9. #9
    Registered User
    Join Date
    06-05-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2007
    Posts
    29

    Question Re: Add Values only if Matches with criteria

    No this is same situation just the cell numbers are changed.
    here K2 is passing number of cells to be add.
    and AI is area name Ak is cell value to be added.
    and in BJ and BK i want output.

  10. #10
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Add Values only if Matches with criteria

    t3.xls
    Please see attached

  11. #11
    Registered User
    Join Date
    06-05-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Add Values only if Matches with criteria

    Hello Sir,
    Still answer is not matching what I am looking for.
    Might be I am missing some words to explain it fully.

    File attach contain the answer I am looking for Please see cell number BJ 10,11,12 n 14

    Value should be this only.
    For counting purpose we can use Week Cell also(AJ) we will add upto 3 weeks only.(I dont know weather this week will be helpfull or not)

    as AK2 is 3 If AK2 is 4 we will add upto 4 weeks.
    Irrespective weather occurrence come or not.

  12. #12
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Add Values only if Matches with criteria

    Which file?

  13. #13
    Registered User
    Join Date
    06-05-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Add Values only if Matches with criteria

    Last edited by Ashurk77; 01-31-2014 at 10:33 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 09-26-2013, 12:16 PM
  2. If values in 2 cells matches criteria, add text to a third cell
    By Zarkov in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-31-2011, 03:14 PM
  3. SUM values when criteria matches more than one cell
    By ABSTRAKTUS in forum Excel General
    Replies: 3
    Last Post: 12-10-2010, 03:53 PM
  4. Replies: 6
    Last Post: 04-21-2009, 07:00 PM
  5. [SOLVED] V-lookup and summing values if more than 1 matches criteria
    By holcay in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-06-2006, 11:55 AM

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