+ Reply to Thread
Results 1 to 17 of 17

multiple conditions, within multiple conditions

  1. #1
    Registered User
    Join Date
    04-06-2008
    Posts
    14

    multiple conditions, within multiple conditions

    Hi All,

    Just cannot figure out how to create a formula based on the following criteria

    Criteria 1
    Match $B2 with Data!$A$2:$A$175

    Then Criteria 2
    Match a range ---- (Data!$E$2:$E$175=">=11"),--(Data!$E$2:$E$175="<=20")

    Then provide sum of (Data!$B$2:$D$175)

    I am using
    =SUMPRODUCT(----(Data!$A$2:$A$175=$B2),--(Data!$E$2:$E$175=">=11"),--(Data!$E$2:$E$175="<=20"),Data!$B$2:$D$175)

    but getting #value, and its because, well, I don't know!!! .. not to good with Excel as it is!

    Can someone help me please?

    Cheers

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

    Re: multiple conditions, within multiple conditions

    What is the data in the range?
    Ali


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

    Forum Rules (updated August 2023): please read them here.

  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,000

    Re: multiple conditions, within multiple conditions

    guessing .....

    =SUMPRODUCT((Data!$A$2:$A$175=$B2)*(Data!$E$2:$E$175>=11)*(Data!$E$2:$E$175<=20),Data!$B$2:$D$175)
    Last edited by JohnTopley; 05-25-2017 at 01:50 AM.

  4. #4
    Registered User
    Join Date
    04-06-2008
    Posts
    14

    Re: multiple conditions, within multiple conditions

    Hi AliGW

    The data in the range of Data!$B$2:$D$175 is numeric
    The data in the range of Data!$E$2:$E$175 is also numeric

    Other data in other ranges are text based

    Hope this helps
    Chuck

  5. #5
    Registered User
    Join Date
    04-06-2008
    Posts
    14

    Re: multiple conditions, within multiple conditions

    Quote Originally Posted by JohnTopley View Post
    guessing .....

    =SUMPRODUCT((Data!$A$2:$A$175=$B2)*(Data!$E$2:$E$175>=11)*(Data!$E$2:$E$175<=20),Data!$B$2:$D$175)
    Hi John,

    Unfortunately, same outcome as previous .. #value


  6. #6
    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,000

    Re: multiple conditions, within multiple conditions

    #VALUE errors are data mismatch (and my guess looks incorrect from your last post!)

    Attach a sample workbook (not image).

    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.

    Remember to desensitize the data.

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

  7. #7
    Registered User
    Join Date
    04-06-2008
    Posts
    14

    Re: multiple conditions, within multiple conditions

    Hi All,

    I made sure that numeric values were set to numeric where it is required (as you noted data type mismatch) ..

    I am getting old, so I am pretty sure I am missing something

    Here is the sample data you requested .. also, I put some manual figures in there based on the filter of the main report table

    Thanks for your assistance
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-06-2008
    Posts
    14

    Re: multiple conditions, within multiple conditions

    Hi All,

    Just providing an update .. it seems that the issue was using

    Data!$B$2:$D$175

    Once I removed the array, and only looked up one column (instead of 3 columns), values were shown. I was under the impression you can calculate multiple columns in one shot .. seems you cannot

    with this, seems I have to do 3 Sumproducts to pull this off .. one for each column of that initial range

    Cheers

  9. #9
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: multiple conditions, within multiple conditions

    Quote Originally Posted by cvanoosbree View Post
    I was under the impression you can calculate multiple columns in one shot
    You can, but only if you use the 'product' form of SUMPRODUCT, as opposed to the 'native', i.e. comma-separated form which you are attempting.

    So use (in C3):

    =SUMPRODUCT((Master!$A$2:$A$175=$B3)*(Master!$E$2:$E$175>=20)*Master!$B$2:$D$175)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  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,000

    Re: multiple conditions, within multiple conditions

    Try

    =SUMPRODUCT((Master!$A$2:$A$175=$B3)*(Master!$E$2:$E$175>=20)*(Master!$B$2:$D$175))

    Should there be an additional criterion for the data in row 2 ?? "Up to nnn Mbps"

  11. #11
    Registered User
    Join Date
    04-06-2008
    Posts
    14

    Re: multiple conditions, within multiple conditions

    Quote Originally Posted by JohnTopley View Post
    Try

    =SUMPRODUCT((Master!$A$2:$A$175=$B3)*(Master!$E$2:$E$175>=20)*(Master!$B$2:$D$175))

    Should there be an additional criterion for the data in row 2 ?? "Up to nnn Mbps"
    To remove complexity, I took out the range value and just created a fixed number (reduced overall forumula length), nothing more.

    I did try your formula, however, if I do a manual lookup of Combined k12 & 600, I get 1267
    If I try using the formula, I get 15907 ... which is not the value I was expecting ... I am assuming its multiplying numbers, but I just need to sum up the numbers within the defined array of the criteria (did that make any sense)

    Is there a way I can keep the multiplication from happening within SUMPRODUCT? I tried NOT using *, but the values were still not what the filtered number is

    Cheers

  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,000

    Re: multiple conditions, within multiple conditions

    The issue is the sumproduct does not consider the criteria in row 2.

  13. #13
    Registered User
    Join Date
    04-06-2008
    Posts
    14

    Re: multiple conditions, within multiple conditions

    I am not sure what you mean by, not consider the criteria in row 2..

    If I changed row 2 to a fixed value .. rather than say .. "upto 600", and used 600 instead, still using the formula
    =SUMPRODUCT((Master!$A$2:$A$175=$B5)*(Master!$E$2:$E$175=I2)*(Master!$B$2:$E$175))

    using the same criteria of K12 & 600, the formula still gives me 2467, instead of 1267 (like the normal filter)

  14. #14
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: multiple conditions, within multiple conditions

    Quote Originally Posted by cvanoosbree View Post
    =SUMPRODUCT((Master!$A$2:$A$175=$B5)*(Master!$E$2:$E$175=I2)*(Master!$B$2:$E$175))

    using the same criteria of K12 & 600, the formula still gives me 2467, instead of 1267 (like the normal filter)
    Because you are now summing as far as column E, as highlighted. Why?

  15. #15
    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,000

    Re: multiple conditions, within multiple conditions

    Try

    in C3

    =SUMPRODUCT((Master!$A$2:$A$175=$B3)*(Master!$E$2:$E$175>=11)*(Master!$E$2:$E$175<=C$2)*(Master!$B$2:$D$175))

    in C4 and copy across

    =SUMPRODUCT((Master!$A$2:$A$175=$B3)*(Master!$E$2:$E$175>=C$2+1)*(Master!$E$2:$E$175<=D$2)*(Master!$B$2:$D$175))

    See attached where I changed headings in row 2..

    You may need to adjust "Satellite" formula.
    Attached Files Attached Files
    Last edited by JohnTopley; 05-25-2017 at 04:05 AM.

  16. #16
    Registered User
    Join Date
    04-06-2008
    Posts
    14

    Re: multiple conditions, within multiple conditions

    Hi All,

    Just wanted to say thank you very much for your assistance with this. Johns modification worked like a charm

    Unsure though as to why I was getting #value error before when no datatypes had changed .. regardless, thanks everyone

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

    Re: multiple conditions, within multiple conditions

    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: 29
    Last Post: 01-01-2017, 10:25 AM
  2. Replies: 2
    Last Post: 01-14-2016, 03:53 PM
  3. [SOLVED] Macro for data present in multiple rows and columns following multiple conditions
    By macrolearnerkk in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-07-2015, 11:20 AM
  4. [SOLVED] Vlookup - Country conditions (Multiple conditions)
    By dluhut in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2013, 11:42 AM
  5. Replies: 0
    Last Post: 08-14-2012, 01:18 PM
  6. Replies: 1
    Last Post: 12-05-2010, 04:39 AM
  7. [SOLVED] How to multiple conditions to validate more than 2 conditions to .
    By Bhuvana Govind in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2005, 04:06 PM

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