Feb 03 2021 09:31 AM
I am trying to figure out a formula for Index/Match to return the gross value for certain rows for each employee. The problem is the array for each employee can change depending on if they have or have not sold items under certain categories. (for example, some employees may not have a "internal" row under their name on one week, but it will be there the next)
is there a way to use the employees name (ex: john smith) and have an array that will end at the next employees name (ex: Bill Hunt)
Sales | Returns | Net Sales | Gross | GP% | Ret % | |
John Smith | 199,166 | -2,238 | 196,929 | 73,578 | 37.36293127 | -1.136198717 |
Customer Pay | 84,093 | 0 | 84,093 | 35,414 | 42.11262846 | 0 |
Warranty | 59,366 | 0 | 59,366 | 24,599 | 41.43601366 | 0 |
Gas, Oil & Grease | 13,672 | 0 | 13,672 | 5,095 | 37.26491238 | 0 |
Tires | 27,009 | 0 | 27,009 | 4,224 | 15.64041825 | 0 |
Internal | 5,508 | 0 | 5,508 | 1,933 | 35.08944878 | 0 |
Counter | 7,091 | -2,194 | 4,897 | 1,644 | 33.57097346 | -44.80513646 |
Accessories | 1,618 | 0 | 1,618 | 467 | 28.85099176 | 0 |
Wholesale | 809 | -44 | 766 | 203 | 26.51909892 | -5.680705191 |
Bill Hunt | 140,685 | -1,299 | 139,386 | 48,271 | 34.63089221 | -0.931945454 |
Customer Pay | 51,502 | 0 | 51,502 | 20,754 | 40.29749634 | 0 |
Warranty | 38,645 | 0 | 38,645 | 13,908 | 35.98778016 | 0 |
Wholesale | 17,910 | -112 | 17,799 | 4,214 | 23.67643936 | -0.627016313 |
Counter | 8,333 | -1,033 | 7,300 | 3,079 | 42.17629669 | -14.15570966 |
Internal | 5,638 | 0 | 5,638 | 2,174 | 38.56080936 | 0 |
Tires | 12,912 | 0 | 12,912 | 2,060 | 15.95610387 | 0 |
Gas, Oil & Grease | 4,686 | -52 | 4,634 | 1,732 | 37.3768829 | -1.123915577 |
Accessories | 1,058 | -102 | 956 | 350 | 36.58600351 | -10.66573245 |
Feb 03 2021 01:47 PM
You shall instruct Excel somehow which rows are with names and which are with items. As variant add helper column and mark in it each row with name; or keep blank row before each name, etc. Any formal logic you can implement, based on it to select name block.
Feb 03 2021 02:51 PM - edited Feb 03 2021 02:52 PM
@SisLilFeathers wow that is a less than ideal set up for the table. I also don't understand what you mean by "return the gross value for certain rows for each employee". But whatever it is, may be possible if you have either a list of all employees or a list of all possible categories (and of course there is no overlap). Here is an example I did of a cumulative sum of Gross for all subitems below that line until it reaches the next name:
=SUMPRODUCT($E22:$E$41*(ROW($E22:$E$41)>ROW($E22))*(ROW($E22:$E$41)<IFERROR(AGGREGATE(15,6,ROW($A23:$A$41)/ISNUMBER(SEARCH($A23:$A$41,CONCAT($J$22:$J$23))),1),ROW($A$41))))