# Sum weekly total of (*) at end of month for Company(*)

1. ## Sum weekly total of (*) at end of month for Company(*)

Hi Everyone,

Need a little help in trying to get the correct formula for this data-set. currently I have =LOOKUP(2,1/(Sheet2!\$A\$3:\$A\$250=\$A2)/(Sheet2!\$B\$3:\$E\$250=B\$2)) though it is not the correct total I am looking for..

So.. Sheet two is a weekly total of issues each company has ( e.g. B3 to E3 )and I would like the total for those weeks (the full month e.g. B3+C3+D3+E3 = Total) to show up on Sheet 1 B2 based on the company name. and repeat for the each month after that.

Thankyou!

2. ## Re: Sum weekly total of (*) at end of month for Company(*)

You have a single space at the end of each of your labels in B1:D1 of the Data Register sheet, so you need to get rid of those, and then you can use this formula in B2:

=SUM(INDEX('Data-set'!\$B\$3:\$M\$8,MATCH(\$A2,'Data-set'!\$A\$3:\$A\$8,0),MATCH(B\$1,'Data-set'!\$B\$1:\$M\$1,0)):INDEX('Data-set'!\$B\$3:\$M\$8,MATCH(\$A2,'Data-set'!\$A\$3:\$A\$8,0),MATCH(B\$1,'Data-set'!\$B\$1:\$M\$1,0)+3))

Copy this across and down, as required.

Hope this helps.

Pete

3. ## Re: Sum weekly total of (*) at end of month for Company(*)

Hi petsean,

I did this problem using the Power Query tool of Unpivot and then used the new table in a Pivot. No formulas needed.

Unpivot using PQ then Pivot Table.xlsx

4. ## Re: Sum weekly total of (*) at end of month for Company(*)

Thank you pete that worked perfectly and it is much appreciated

as well thankyou marvin, that is actually a good idea to use pivot tables, not sure why I didn't think of it earlier!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1