Summing duplicate values(matches) from hlookup, for EACH ROW.

Dear Excel Legends,

I have spent my whole day to solve this one but no luck. I've attached my dataset file.

What I am trying to do here is that I want to come up with a function that can sum the 'count' for EACH 'category' for EACH ROW. This dataset unfortunately has to be in this format and cannot be combined, merged, or divided. So for example, if you look at the second row, you will be able to see that there are three occurrence of Category 1 (B2, D2, and J2) and what i want to do is essentially summing up the 'count' accompanied by the each cell. So here, I want to sum A2(=77), C2(=62), and I2(=71).

I tried many things - the hlookup only gives me the first occurrence so failed, and when I tried to use SUMIF, SUMIFS, and IF, I couldn't figure it our as my 'Category' values are numeric, so I failed to distinguish the values.

Basically I want to make a function to command 'sum the Count only if the Category is 1.' Any way to do this????

how about the null categories?

Please Login or Register  to view this content.

The routine above will sumup the categories and place the total in the appropiate column
Yet there are categories that were left empty.

Could you expand it a bit more??

Oh I just saw this reply under your first one, and wow, this is just a magic!! Thank you so much. The beauty of VBA...

Hi,

I was writing some code in the attached workbook before you received your response, but figured I would post this anyway. The attached workbook has VBA code that is triggered when any of the values in the cells from Columns A thru T are changed and updates the totals. It only looks at Categories 1 - 10 since those are the categories that had headings.

Thanks,

Dan

Originally Posted by djbomaha

Hi,

I was writing some code in the attached workbook before you received your response, but figured I would post this anyway. The attached workbook has VBA code that is triggered when any of the values in the cells from Columns A thru T are changed and updates the totals. It only looks at Categories 1 - 10 since those are the categories that had headings.

Thanks,

Dan

Hi Dan, Thank you so much for your help. You guys are just legends...

Originally Posted by humdingaling

Formula solution
put in V3

Formula:

Please Login or Register  to view this content.

Oh wow even without VBA works?? Thank you so much!! I really learned a lot from here

Originally Posted by humdingaling

Sorry forgot about the header

Formula:

Please Login or Register  to view this content.

Thanks so much!! One quick question, do you mind explaining a bit more about the 'COLUMN(A1)' part in the formula??