+ Reply to Thread
Results 1 to 12 of 12

ARRAY FORMULA With IF, AND/OR Functions

  1. #1
    Registered User
    Join Date
    10-10-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    52

    ARRAY FORMULA With IF, AND/OR Functions

    Hi,

    Hopefully someone can help with completing this array formula for me.
    I have attached a sample of data I am using. The data in C1:C5 is the output from a multiple selection combo box. This is then translated to the data in E1:E5 (so sometimes there are only 1 result or sometimes five results. I am then using this information as part of the formulas shown in C10:C13. The CSE formulas I am using work but I couldn't figure out how to incorporate the options in B7 (highlighted in RED) that I now have to add to this file. These options don't actually appear in the data itself but summarise Column D in the DATA tab as follows:
    All Bands - All the Deal Bands
    Band 3+ - only Bands 3 and 4
    Band 0-2 - only Bands 0,1 and 2

    I figure I need to incorporate nested IF Statements into my formula but have failed to make anything work. If there are other ways of doing this, I am willing to consider them.

    Thanks for your help

    Regards
    Al
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: ARRAY FORMULA With IF, AND/OR Functions

    You can use this array* formula in C10:

    =SUM(COUNTIFS(DATA!$C$2:$C$88,$C$8,DATA!$B$2:$B$88,$B10,DATA!$F$2:$F$88,$E$1:$E$5,DATA!$D$2:$D$88,IF($B$7="Band 3+",">=3",IF($B$7="Band 0-2","<=2","<>"))))

    (changes shown in red), then copy down.

    *Note that you still need to use CSE to commit the formula.

    Hope this helps.

    Pete

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: ARRAY FORMULA With IF, AND/OR Functions

    In Column E

    =IF($B$7="All Bands",$D1,IF($B$7="Band 0-2",IF(ROW()>3,"",$D1),IF(ROW()<4,"",INDIRECT("D"&ROW()))))

    and copy down

    in C10

    =SUMPRODUCT((DATA!$C$2:$C$88=$C$8)*(DATA!$B$2:$B$88=$B10)*((DATA!$F$2:$F$88=$E$1)+((DATA!$F$2:$F$88=$E$2)*(DATA!$F$2:$F$88=$E$3)+((DATA!$F$2:$F$88=$E$4)*(DATA!$F$2:$F$88=$E$5)))))

    Copy down

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ARRAY FORMULA With IF, AND/OR Functions

    Quote Originally Posted by JohnTopley View Post
    =SUMPRODUCT((DATA!$C$2:$C$88=$C$8)*(DATA!$B$2:$B$88=$B10)*((DATA!$F$2:$F$88=$E$1)+((DATA!$F$2:$F$88=$E$2)*(DATA!$F$2:$F$88=$E$3)+((DATA!$F$2:$F$88=$E$4)*(DATA!$F$2:$F$88=$E$5)))))
    Is that last multiplication sign supposed to be a plus sign?

    If so, you can replace all the + criteria with a single ISNUMBER(MATCH criteria.

    --ISNUMBER(MATCH(DATA!$F$2:$F$88,$E$1:$E$5,0))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: ARRAY FORMULA With IF, AND/OR Functions

    Yes ....typo .. It should be "+"

    so formula would be ...??

    =SUMPRODUCT((DATA!$C$2:$C$88=$C$8)*(DATA!$B$2:$B$88=$B10)*(--ISNUMBER(MATCH(DATA!$F$2:$F$88,$E$1:$E$5,0))

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ARRAY FORMULA With IF, AND/OR Functions

    When applicable, I prefer to use the -- syntax:

    =SUMPRODUCT(--(DATA!$C$2:$C$88=$C$8),--(DATA!$B$2:$B$88=$B10),--ISNUMBER(MATCH(DATA!$F$2:$F$88,$E$1:$E$5,0)))

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: ARRAY FORMULA With IF, AND/OR Functions

    @Tony .... Thank you.

  8. #8
    Registered User
    Join Date
    10-10-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: ARRAY FORMULA With IF, AND/OR Functions

    Thanks very much. Pete - your solution works like a charm.

    John - I think I might be confusing myself with your solution as its not producing the right numbers. I think your first option says I should change my current formulas in Column E? Doing this overrides the information about the deal types driven by True/False conditions from my combobox. Did you mean to do this? Didn't you mean to create another column that translates the actual deal grades? I can also see that the Grades from Column D no longer appear in the final formula so not sure how this formula can get the correct result.

    I have redone your formula and this actually works now.
    In Column F I entered the deal grades below
    0
    1
    2
    3

    In Column G, I used the updated formula:
    =IF($B$7="All Bands",$F1,IF($B$7="Band 0-2",IF(ROW()>3,"",$F1),IF(ROW()<4,"",INDIRECT("f"&ROW()))))

    In C10, I then entered the following formula (edited as per Tony’s feedback)
    =SUMPRODUCT((DATA!$C$2:$C$88=$C$8)*(DATA!$B$2:$B$88=$B10)*(DATA!$F$2:$F$88=$J$1:$N$1)*(--ISNUMBER(MATCH(DATA!$D$2:$D$88,$G$1:$G$5,0))))

    To get the above to work I had to transpose the data in E1:E5 to J1:N1

    Apologies if I missed the point of what you were trying to do, but learning a lot.

    Regards,
    Al

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ARRAY FORMULA With IF, AND/OR Functions

    You're welcome!

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: ARRAY FORMULA With IF, AND/OR Functions

    @Al,
    I should have explained that the formula in E replaced the need to the TRUE/FALSE you were using. As it is all now working ....

  11. #11
    Registered User
    Join Date
    10-10-2009
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: ARRAY FORMULA With IF, AND/OR Functions

    @John
    I thought so. Unfortunately those True/Falses are simulating output from a multi select ComboBox where the user chooses the Deal Types they want to view. Probably there is a better way of achieving the same end?

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: ARRAY FORMULA With IF, AND/OR Functions

    If you are OK with TRUE/FALSE then go with it: no need to add complications (me culpa!)

+ 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. VBA – User Designed Functions (UDF) - Renaming array functions
    By hbsonly in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-03-2013, 02:00 PM
  2. Replies: 3
    Last Post: 03-20-2012, 02:43 AM
  3. Replies: 1
    Last Post: 02-10-2012, 05:27 PM
  4. using excel array functions on part of array
    By chewwy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2009, 07:19 AM
  5. Help condensing a formula using new functions or array
    By Mercanthrope in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-09-2008, 07:44 PM
  6. Replies: 2
    Last Post: 07-13-2006, 11:30 PM
  7. Array Formula w/ COLUMN & MATCH FUNCTIONS
    By SJT in forum Excel General
    Replies: 4
    Last Post: 06-26-2006, 01:20 PM
  8. Replies: 3
    Last Post: 09-08-2005, 12:53 AM

Tags for this Thread

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