Offset function help

[Joseph Horling]

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.

 

[herofox]

hello
you can use this

=SUMPRODUCT($B$7:$J$150,(($B$6:$J$6=$A$2)*($A$7:$A$150=$A$3)))

 

[Joseph Horling]

Great, thanks herofox!

 

[bosco_yip]

An example using Offset function, it will faster than the SUMPRODUCT function.



In B2, enter formula :

=OFFSET(A6,MATCH(A3,A7:A100,0),MONTH(0+(1&A2)))

 

[GraH - Guido]

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))

 

[shrivallabha]

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)

 

[Peter Bartholomew]

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.

 

[GraH - Guido]

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.

 

[Peter Bartholomew]

Just to take Guido's post a step further, it is also possible to take range intersection a step further and introduce intersections with multicell ranges. In the case below, the formula

= Happy NewYear _2020

returns @CHANDOO

 

[Joseph Horling]

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.

 

[bluesky63]

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

 

[Joseph Horling]

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.

 

[bluesky63]

I am using power query, so your excel need to be 2010 and above (2010, 2013 need add-on)