Data Aggregations' with Index and Matich

Hi,

I need to aggregate some non- adjacent columns into a consolidated report that is updatable. I am thinking index and match can solve my problem but I just cant seem to figure it out. I have attached a small sample workbook that is representative of my issue. I hope someone can help.

Chris

Hi
Try this :

D19 =SUMPRODUCT((OFFSET($O$17,MATCH($C19,$O$18:$O$30,0),1,,COLUMNS($P$17:$X$17)))*(ISNUMBER(SEARCH(D$18,$P$17:$X$17))))

E19 =SUMPRODUCT((OFFSET($O$17,MATCH($C19,$O$18:$O$30,0),1,,COLUMNS($P$17:$X$17)))*((ISNUMBER(SEARCH("*SHOE*",$P$17:$X$17)))+(ISNUMBER(SEARCH("*GLOVE*",$P$17:$X$17)))))

F19 =SUMPRODUCT((OFFSET($O$17,MATCH($C19,$O$18:$O$30,0),1,,COLUMNS($P$17:$X$17)))*((ISNUMBER(SEARCH("*HELMET*",$P$17:$X$17)))+(ISNUMBER(SEARCH("*BAT*",$P$17:$X$17)))+(ISNUMBER(SEARCH("*BARBEL*",$P$17:$X$17)))+(ISNUMBER(SEARCH("*STIC*",$P$17:$X$17)))))

Hi Belinda200

Yes this works but...My true data table is 15 columns that need to be combined down to 8. These are non adjacent columns. Is their an easier way? May be a sumifs with index and Match....?

Chris

What were you thinking about? Please apply it to the file and let the forum help you fix the formula you came up with.

Anyway - here's without the offset:

D19 =SUM((($C19=$O$18:$O$30)*(ISNUMBER(SEARCH("*ball*",$P$17:$X$17)))*($P$18:$X$30)))

E19 =SUM((($C19=$O$18:$O$30)*((ISNUMBER(SEARCH("*glov*",$P$17:$X$17)))+(ISNUMBER(SEARCH("*sho*",$P$17:$X$17))))*($P$18:$X$30)))

F19 =SUM((($C19=$O$18:$O$30)*((ISNUMBER(SEARCH("*helmet*",$P$17:$X$17)))+(ISNUMBER(SEARCH("*stic*",$P$17:$X$17)))+(ISNUMBER(SEARCH("*bat*",$P$17:$X$17)))+(ISNUMBER(SEARCH("*barbel*",$P$17:$X$17))))*($P$18:$X$30)))