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 likeHowever, it returns "1" if at least 1 "Selected" flag is in the specified ranges, while I need to count themFormula:Please Login or Register to view this content.
Bookmarks