+ Reply to Thread
Results 1 to 8 of 8

Need a workable IFAND function formula for multiple criteria or maybe an Array formula?

  1. #1
    Forum Contributor
    Join Date
    09-08-2016
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    110

    Need a workable IFAND function formula for multiple criteria or maybe an Array formula?

    Hi All,

    I have rows of an order# in a column, rows of differing months that corresponds to the order number in another column, rows of different years that corresponds to the order# and months in another column and finally, rows of a sales percentage corresponding to the order#, months and years.

    I have built a data validation list so that I can change order numbers and have the corresponding sales percentage change for the specific month and year as I change the order #'s.

    The attached images shows how my data is laid out...

    I have tried the following formula....

    =if(and($B$2(order#)=$F$2:$F$100(range of order#'s),D4(Month)=$G$2:$G$100(range of months),C5(Year)=$H$2:$H$100(range of years),$I$2:$I$100(range of sales %),"")

    This formula returns blanks for everything. I am trying to populate the second spreadsheet with the order %'s so that I can create graphs.

    Any help is appreciated.

    Thanks.
    Attached Files Attached Files
    Last edited by jeptik; 10-18-2016 at 02:13 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,372

    Re: Need a workable IFAND function formula for multiple criteria

    We cannot work with images. Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Because browsers behave differently, many of our members cannot see uploaded images. Do not upload a picture of your file as this will only delay getting solutions.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Contributor
    Join Date
    09-08-2016
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    110

    Re: Need a workable IFAND function formula for multiple criteria

    Thanks Ali.

    I will do a mock up and repost.

  4. #4
    Forum Contributor
    Join Date
    09-08-2016
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    110

    Re: Need a workable IFAND function formula for multiple criteria

    If I am not making it clear, please let me know and I will try my best to make it easier to understand as I need assistance in figuring this out today.

    Thanks.
    Last edited by jeptik; 10-18-2016 at 12:36 PM.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,264

    Re: Need a workable IFAND function formula for multiple criteria or maybe an Array formula

    In I4

    =SUMIFS($D:$D,$A:$A,$G$1,$B:$B,I$3,$C:$C,$H4)

    copy across and down

  6. #6
    Forum Contributor
    Join Date
    09-08-2016
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    110

    Re: Need a workable IFAND function formula for multiple criteria or maybe an Array formula

    Thank you John!!!

    Worked like a charm!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,372

    Re: Need a workable IFAND function formula for multiple criteria

    Quote Originally Posted by jeptik View Post
    If I am not making it clear, please let me know and I will try my best to make it easier to understand as I need assistance in figuring this out today.

    Thanks.
    Sorry - I didn't see that you had attached the file. Looks like John has sorted you out, though.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,264

    Re: Need a workable IFAND function formula for multiple criteria or maybe an Array formula

    Thank you for the feedback and rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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: 3
    Last Post: 09-26-2016, 02:42 PM
  2. [SOLVED] IFAND Function with positive and negative conditions
    By jeptik in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-08-2016, 12:30 PM
  3. [SOLVED] IFAND 5 criteria
    By makinmomb in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-18-2015, 01:42 PM
  4. use of ifand And function
    By rajuj in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-16-2013, 10:23 PM
  5. I need some help with an IFAnd formula; real confussed.
    By susiesc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-25-2013, 11:19 AM
  6. Question on IFAND formula in Excel - & is it the best method
    By galvinpaddy in forum Excel General
    Replies: 2
    Last Post: 12-14-2011, 11:47 AM
  7. Gnatt chart - Need help with Ifand formula
    By Kristina1976 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-10-2011, 02:05 PM

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