• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Offset function help

J

Joseph Horling

Member
Hi Chandoo Experts,
Just wondering if I could get some help from you using the offset function. I used the offset function with sum and count and countif in cell B2 to get the sum of sales for the month. Added names to offset and it seemed to work. In cell b3, I would like to get just the value the sales person in cell a3 coupled with the month in a2. Tried a couple different combos but I couldn't get it to work. Can you try something for me? Thanks, Joe from Michigan.
 

Attachments

Hi all,
I just noticed the SUMPRODUCT solution will work regardless of regional settings and the OFFSET solution may not. This part MONTH(0+(1&A2))] requires the month names which are used in the drop down to be written in the local language matching the regional settings. e.g. selecting "MAY" returned an error #VALUE! because "1May" is not a valid date in my case, whereas "1Mei" (in Dutch) does.

A workaround while still using OFFSET is to replace the third argument by a second MATCH to get the correct column number. Then I believe using the combo INDEX-MATCH-MATCH might be preferred over OFFSET (and SUMPRODUCT).

Converting the data range to a table enables one to use structured references, making it more readable and future proof. Adding rows/columns does not require the formula to be updated. I've also used named cells to refer to the drop down selections: "cName" and "cMth".
[B2]=INDEX(tSales,MATCH(cName,tSales[Sales],0),MATCH(cMth,tSales[#Headers],0))
 

Attachments

You can also use INDEX/MATCH as well.

=INDEX(B7:J15,MATCH(A3,A7:A15,0),MATCH(A2,B6:J6,0))

Also a VLOOKUP as well

=VLOOKUP(A3,A6:J15,MATCH(A2,A6:J6,0),0)
 
The non-volatile function INDEX can also be used, in this case with a table for the sales figures to ensure the calculation responds dynamically to changes in the dataset.
= SUM( INDEX( Sales, MATCH( Name, salesPerson, 0 ), 0 ) )

The result for a specific month would be returned by an INDEX/MATCH/MATCH formula
= INDEX( Sales, MATCH( Name, salesPerson, 0 ), MATCH( month, Sales[#Headers], 0 ) )

In future versions of Office 365 you will be able to use
= SUM( XLOOKUP( Name, salesPerson, Sales ) )
and
= XLOOKUP( Name, salesPerson, Sales ) XLOOKUP( month, Sales[#Headers], Sales )

The last formula is unusual in that it consists of a Range intersection of a table row and column.
 

Attachments

Last edited:
Along the same road of the intersection example given by Peter not requiring Office 365 insider release.
Select the table and create Named Ranges from selection (top row and left column).
formula becomes =INDIRECT(cMth) INDIRECT(cName). There is a space " " between both arguments for the intersection.
But indirect is volatile.
 

Attachments

Just wanted to say thank you to all of you for the responses. There is some great stuff here and I appreciate you helping me out. Chandoo is awesome.
 
You can also unpivot in Power query and load to excel and insert slicers for Month/Names, it is easier to update, just insert sales in the empty cell in Source worksheet, right click on power query worksheet table > refresh, it will be updated
Cheers
 

Attachments

Hi Bluesky63,
Thanks for the response. That is cool. Except when I try to refresh, it says the initial data source failed. Not sure how to correct that. Can you tell me how? Thanks, Joe.
 
You must log in or register to reply here.
Back
Top