Index/Match Array Issue

Regular Visitor

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)

 

 

 SalesReturnsNet SalesGrossGP%Ret %
John Smith199,166-2,238196,92973,57837.36293127-1.136198717
Customer Pay84,093084,09335,41442.112628460
Warranty59,366059,36624,59941.436013660
Gas, Oil & Grease13,672013,6725,09537.264912380
Tires27,009027,0094,22415.640418250
Internal5,50805,5081,93335.089448780
Counter7,091-2,1944,8971,64433.57097346-44.80513646
Accessories1,61801,61846728.850991760
Wholesale809-4476620326.51909892-5.680705191
Bill Hunt140,685-1,299139,38648,27134.63089221-0.931945454
Customer Pay51,502051,50220,75440.297496340
Warranty38,645038,64513,90835.987780160
Wholesale17,910-11217,7994,21423.67643936-0.627016313
Counter8,333-1,0337,3003,07942.17629669-14.15570966
Internal5,63805,6382,17438.560809360
Tires12,912012,9122,06015.956103870
Gas, Oil & Grease4,686-524,6341,73237.3768829-1.123915577
Accessories1,058-10295635036.58600351-10.66573245
2 Replies

@SisLilFeathers 

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.

@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))))

 

 

mtarler_0-1612392675537.png