+ Reply to Thread
Results 1 to 5 of 5

VBA - SumIfs multiple conditions in array

  1. #1
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    VBA - SumIfs multiple conditions in array

    Hi guys,

    I´ve spent better part of the day trying to solve this baddie and I just cant get the result I´m looking for. So the question at hand is, Is there any function / VBA code that would solve this within, lets say, 4-5 lanes of coding (not counting declarations).

    Desired outcome is Sum of column A when the condition is met. Condition consists of 6 variables (there is a pool of numbers from which you make the group which i need to scan, whether they are in group H1-H5; there may be 1-3 numbers to check against the group; and same applies for group O1-O5...checking 1 to 3 numbers if they are there or not). So lets set an example, I have numbers 11,12 for group H1-H5 and numbers 51, 81. I need a Sum of column A where all 4 numbers are present on relevant row - result for my example should be "1" if I´m not mistaken. Order is irrelevant.

    Hopefully I was clear about the goal - so far best solution I came up with was huge pile of Ifs which is not optimal.

    TAB:
    A B C D H1 H2 H3 H4 H5 O1 O2 O3 O4 O5
    1 0 0 0 77 66 11 12 56 21 51 81 42 72
    0 1 0 0 22 55 88 23 78 31 61 91 52 82
    1 0 0 0 33 66 99 45 89 41 71 32 62 92
    0 1 0 0 11 44 77 12 56 21 51 81 42 72
    1 0 0 0 22 55 88 23 78 31 61 91 52 82
    0 1 0 0 33 66 99 45 89 41 71 32 62 92
    1 0 0 0 77 44 11 66 56 21 51 81 42 72
    0 1 0 0 22 55 88 23 78 31 61 91 52 82
    1 0 0 0 33 66 99 45 89 41 71 32 62 92


    Lets see what you can come up with - also solutions that take whole page in VBA editor is not optimal either (same as my Ifs). I need a code, that i can read and understand with ease even if i come to this file 2 years later.

    Thx for trying

    Soul
    Regards,
    Soul

    If you liked my help, consider raising my reputation by clicking that button, and please don?t forget to mark this thread [SOLVED], when you?re done here.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: VBA - SumIfs multiple conditions in array

    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.

    Remember to desensitize the data.

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

  3. #3
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: VBA - SumIfs multiple conditions in array

    Edit button seems to be on holidays already, anyway....needed to explain one part a bit more.....condition consists of UP to 6 variables (it may be 1 + 0; 1 + 1; 2 + 1 .... 1 + 3 .... 3 + 3) numbers of the pool

  4. #4
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: VBA - SumIfs multiple conditions in array

    Quote Originally Posted by zbor View Post
    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.

    Remember to desensitize the data.

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

    thx for respond...I´ll attach the file, but since there is nothing else but the table i posted there so you see the group of data, u wont be getting much more then just copy pasting what I´ve already shared....rest is not relevant nor public

    hm, for some reason i cant post the file here, i cant edit nor click the respond normal way, seems like my chrome is getting better of me...wierd, but as i said, u can copy paste it from the web up there and use the text to columns with the space delimiter, and that´s just it, that would be in the file if i could share it
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague, Czech rep.
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    138

    Re: VBA - SumIfs multiple conditions in array

    never mind, resolved it with long code but at least readable at any moment in time

+ 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. sumifs with multiple conditions
    By cpg_123 in forum Excel General
    Replies: 9
    Last Post: 07-20-2016, 01:31 PM
  2. SUMIFS with multiple conditions
    By AadVissers in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-21-2016, 12:22 PM
  3. Sumifs multiple conditions
    By ksmith21 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-16-2015, 06:12 AM
  4. [SOLVED] SUMIFS with multiple conditions Formula
    By rz6657 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-09-2015, 03:13 PM
  5. [SOLVED] Sumifs or Sumproduct With Multiple Conditions Help Please!
    By geepee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-01-2013, 09:27 AM
  6. Multiple conditions - SumProduct, SumIfs, CountIfs - Which do I need?
    By Carcophan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-08-2013, 01:43 PM
  7. sumifs multiple conditions stuck
    By kieranbop in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-31-2011, 07:13 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