Copy filtered data to a new row

Hi!

I need excel to copy and sum the data från several rows to a single row when I filter it by a name or so.

My columns looks like this:
Occupation, role, hours, name

When I filter by name and role I need all rows containg the name and role to be gathered in a single row.

How is that done?

I know how to sum the filtered rows if they are containing numbers.
But what about the text (that is same for the filtered rows)?

Thanks in advance.

OK, I struggled myself and notices that it should work with i.e.:
=CELL("contents";G5:G15)

But, if I want to filter by two columns than the row with summary disappears and I want it to get the choice that is filtered.
How do I do that?