+ Reply to Thread
Results 1 to 2 of 2

Kind of SUMPRODUCT that makes UNION operation instead of INTERSECTION

  1. #1
    Registered User
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    2003
    Posts
    17

    Kind of SUMPRODUCT that makes UNION operation instead of INTERSECTION

    I need a a combination of some formulas that makes the same thing as SUMPRODUCT formula, but SUMPRODUCT inherits intersection principle, while I need the union principle. Possibly it should be something with SUMIF...

    Here is the structure of my data: I have multiple columns with "Selected" word as a flag. Columns divided into groups of personnel like: Top management, Middle management, Workers. Each group has 3 subcat: Salary increase, No change, Salary decrease. While rows represent different companies.

    I need to count number of companies that have Salary increase \ No change \ Salary decrease in at least 1 personnel group so that if company A has Salary increase for Workers, then the whole company is thought to be going to increase salary.

    Here is the capture of current structure:
    Capture.PNG
    Different shades of green - are my 5 groups, each having 3 subcategories. Yellow area on the right represents summary on that subcategories and finds if it least 1 subcategory in any group has a flag. After this is done, cell T1 counts "Selected" and represent that number as % of total companies.

    I feel that the formula should look smth like
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    However, it returns "1" if at least 1 "Selected" flag is in the specified ranges, while I need to count them

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,913

    Re: Kind of SUMPRODUCT that makes UNION operation instead of INTERSECTION

    =SUMPRODUCT((((B2:B9="Selected")+(E2:E9="Selected")+(H2:H9="Selected")+(K2:K9="Sel​ected")+(N2:N9="Selected"))>0)*1)
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

+ 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: 5
    Last Post: 06-09-2014, 05:39 PM
  2. Range.columns.count property returns wrong count after union operation
    By gopinan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-13-2008, 04:48 AM
  3. Intersection, union and difference
    By paulinoluciano in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-03-2006, 05:55 AM
  4. sumproduct question - kind of...
    By Poody in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-14-2006, 03:55 PM
  5. Union, intersection, join
    By swchee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2005, 12:05 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