VLOOKUP and Calculation of Columns query

Hi
I Have an excel spreadsheet attached which shows the following (example below) -full file in attached

Date Name Mon Tue Wed Thu Fri Total
06-07-18 Name1 1 1 1 1 1 5
13-07-18 Name1 4 4 2 4 2 16
20-07-18 Name1 5 5 3 5 3 21
27-07-18 Name1 6 6 4 6 4 26
06-07-18 Name2 7 7 5 7 5 31
13-07-18 Name2 8 8 6 8 6 36
20-07-18 Name2 9 9 7 9 7 41
27-07-18 Name2 1 1 8 1 8 19
06-07-18 Name3 2 2 9 2 9 24
13-07-18 Name3 3 3 1 3 1 11
20-07-18 Name3 4 4 2 4 2 16
27-07-18 Name3 5 5 3 5 3 21

I need to create a summary table showing for each of the Name entries Name 1, Name 2, Name 3 etc - I want the totals to be added together and recorded as a Summary answer so the resulting table would look something like

Name 1 68
Name 2 127
Name 3 72

I wondered if it is possible to do the summary table calculations through some clever functions e.g VLookup and Sum but cant get it working.

One other point I had sorted them manually by Name order, but if it doesnt have to be sorted first and still give the same result that would be great.

Thanks for any advice

Maybe use sub totals as shown in the attached.

or... copied down:

=IF(COUNTIF($B$2:B2,B2)=1,SUMIF(B:B,B2,H:H),"")

There is an error in your expected results at name 4.

I suspect Kenkie is on the right ttrack. If so, there is no need for the helper column.

In L2:
=IFERROR(INDEX($B$2:$B$29,MATCH(0,INDEX(COUNTIF($L$1:$L1,$B$2:$B$29),0),0)),"")

In M2
=IF(L2="","",SUMIF(B:B,L2,H:H))

both copied down as far as needed.

I'm away for the night. But yes, you can. The best way is to use a dynamic named range. I'll set one up in the morning.

I's use a named range. CTRL-F3 to view edit:

=Sheet1!$B$2:INDEX(Sheet1!$B$2:B$10000,COUNTA(Sheet1!$B$2:$B$1000))

This will look down 10,000 rows in column B and set the range automatically, to fit the size of your data. It's been called Names. Then the formula in L2 becomes:

=IFERROR(INDEX(Names,MATCH(0,INDEX(COUNTIF($L$1:$L1,Names),0),0)),"")

That said, Kenkie's suggestion will also work perfectly well.

Originally Posted by Glenn Kennedy

I's use a named range.

Ah, me hearty!!!

Pre-caffeine typo... Or Fat Finger Syndrome (they're not, as it happens...).

I'm on my third already - buzzing!!!

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.