How to summarise data in multiple tables?

Question

Hello...

Is there a way I can summerise the data in columns B to E to automatically appear in column K?

The data in columns C to E are hours worked on a specific project by task (3 no. tasks), column B is the names of each person who has worked that week.

I would like the hours worked per person to be summerised in column J & K.

I have highlighted names in colours for reference.

Many Thanks in Advance!

Answer 1

Hi,

In cell J3 apply this formula and drag it down:

=IFERROR(INDEX(C$2:C$18,MATCH(0,INDEX(COUNTIF(J$2:J2,C$2:C$18)+(C$2:C$18="")+ISNUMBER(C$2:C$18),),0)),"")

or apply this formula if you run Microsoft 365 or Excel 2021:

=UNIQUE(FILTER(C2:C18,(C2:C18<>"")*ISTEXT(C2:C18)))

In cell K3 apply this formula and drag it down:

=SUM((C$2:C$18=J3)*(D$2:F$18))

Hope this helps.

Answer 2

Hi Ste_931 ,

I'm Mukesh and I will be happy to help you out with your question.

AutoSum is one of the quickest ways to summarize data. Select a cell to the right or below a range of values and click AutoSum. Excel will enter a SUM() function that references the data above or to the left.

Here is a link for a detailed description of the process that you must follow. Create a summary report in Excel with data consolidation https://www.techrepublic.com/article/create-a-summary-report-in-excel-with-data-consolidation/

https://www.techrepublic.com/article/10-tips-for-summarizing-excel-data/

'Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.'

I hope this information helps. If you have any questions please let me know and I will be glad to help you out.

Regards
Mukesh

Answer 3

Hi,

In cell K3, enter this formula and copy down

=SUMPRODUCT(($B$2:$B$16=J3)*($C$2:$E$16))

Hope this helps.