+ Reply to Thread
Results 1 to 5 of 5

If statement that will look among multiple criteria and generate a statement

  1. #1
    Registered User
    Join Date
    06-27-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    94

    If statement that will look among multiple criteria and generate a statement

    The title may seem a little bit simpler than what I need. What I have is a sumif formula on sheet2 that looks on Sheet1 and adds all the cells that correspond with the word "banana". This works fine. What I am looking for now is a formula that will allow me to look into that sumif formula (or the ranges in the sumif formula) and if there is a blank cell that corresponds with the word "banana", then a statement of "Pricing not available" will generate in a cell on sheet2. I'm sure there is a way to figure this out on my own, but my minds elsewhere and i just can't focus. Any help is appreciated.

  2. #2
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: If statement that will look among multiple criteria and generate a statement

    So.. instead of Total.. you want to display "Pricing Not Available", if any of the ceell in Sum Condition Range is Blank..
    You are using SUMIF...that means only 1 Condition range you are checking..
    and result should be displayed in the same cell where SUMIF applied...
    OOPS so many guess.. I think I should start my MindReader via IP address software..
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  3. #3
    Registered User
    Join Date
    06-27-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: If statement that will look among multiple criteria and generate a statement

    Debraj Roy, thanks for asking for clarification. I think I figured it out, just have to test it. I used a nested if function. The only issue i have now is that one of the cells has a sumif formula that will subtract a previous cell. the original formula was =SUMIF('Fruit '!$B$9:$B$22,"*Fuji*",'Fruit '!$E$9:$E$17) in cell c12 and in cell c13 the formula was =SUMIF('Fruit '!$B$9:$B$22,"*apples*",'Fruit '!$E$9:$E$17)-C12. What i need for the statement is to look through the range, disregard fuji, and just look at corresponding cells to the other apples. if that makes sense.
    in esense i need to figure out this statement
    if (fruit range=apple except for fuji, then True: if (cost range<>"", then True: "", False: "pricing not found"),False: "")
    Not sure how to disregard fuji apples right away.

  4. #4
    Registered User
    Join Date
    06-27-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: If statement that will look among multiple criteria and generate a statement

    never mind. back to the original question. the formula i thought i had figured out, didn't work. darn it.

  5. #5
    Registered User
    Join Date
    06-27-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: If statement that will look among multiple criteria and generate a statement

    Thanks for all the help. I've adjusted what I want to accomplish to just look at the entire section instead of individual lookup criteria.

+ 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