+ Reply to Thread
Results 1 to 12 of 12

Want to know the ingredients of sumif formula

  1. #1
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Want to know the ingredients of sumif formula

    Please check the attached sheet.
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Want to know the ingredients of sumif formula

    hi Sarangsood. you can do it like this:
    Please Login or Register  to view this content.
    i commented off the MsgBox line. that's another way you can do it. remove the line above it & remove the single quote before the MsgBox to try it out

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Want to know the ingredients of sumif formula

    Try this.....with SUMIF

    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: Want to know the ingredients of sumif formula

    @sktneer : i inserted your module. now how should i proceed ?

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Want to know the ingredients of sumif formula

    Click anywhere inside the code and hit F5.

    Please find the attached sheet.
    Here you will find a module SumIf. Run this module as stated above.
    Attached Files Attached Files
    Last edited by sktneer; 11-05-2013 at 03:09 AM.

  6. #6
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: Want to know the ingredients of sumif formula

    but it is giving me the sum of 6700+6800+6900+7000. but i want the cell to tell me 6700+6800+6900+7000 not the sum which is 27400.

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Want to know the ingredients of sumif formula

    If that is the case benishiryo's code will do the trick. Probably I misunderstood your requirement.

  8. #8
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: Want to know the ingredients of sumif formula

    @benishiryo : your code is working fine , but is there a way i can use it as a formula. actually the problem is i am using real time data , so i want them to change automatically.

  9. #9
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Want to know the ingredients of sumif formula

    i'm not really into VBA, so maybe others can chip in. this Function below will allow you to input the range & criteria. downside (if your equation is not always using ">") is that i don't know how to change the operator through the formulas in Excel:
    Please Login or Register  to view this content.
    same thing. just paste inside the VBE window. you can then go back to the spreadsheet & use a new formula i named ResultInfo. you need to put in the range & criteria like:
    =ResultInfo(A1:A6,D1)

  10. #10
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: Want to know the ingredients of sumif formula

    perfect. what i can do for ">" problem is that i can make another vba with different name with "<".

    thanks a lot

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Want to know the ingredients of sumif formula

    Slight change in Beni's Code...

    Please Login or Register  to view this content.
    =EvaluateSumif(A1:A6,">"&D1)

    =EvaluateSumif(A1:A6,"<"&D1)

    =EvaluateSumif(A1:A6,"<="&D1)

    =EvaluateSumif(A1:A6,">="&D1)
    Last edited by :) Sixthsense :); 11-05-2013 at 05:13 AM.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  12. #12
    Forum Contributor
    Join Date
    05-18-2011
    Location
    Delhi , India
    MS-Off Ver
    Excel 2007
    Posts
    171

    Re: Want to know the ingredients of sumif formula

    thanks a lot. solves the problem. thanks a lot sixthsense

+ 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. Showing whole items with only selected ingredients sheet
    By brandedadnan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-01-2013, 02:47 AM
  2. Replies: 1
    Last Post: 04-19-2013, 07:21 AM
  3. Maximum drinks for minimum ingredients.
    By fixit9660 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-19-2013, 03:56 PM
  4. Can I use Excel to Identify Cocktails based on my Ingredients?
    By GiantSquid71 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-19-2012, 11:03 PM
  5. Excel 2007 : Help with prices of ingredients used
    By louise2613 in forum Excel General
    Replies: 2
    Last Post: 06-18-2011, 05:36 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