• 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.

Lookup totals by the months going across the spreadsheet

Hello,

I would like to lookup the total VIC value for a month from the Target tab for VIC using column B to identify State

The value by month should equal as follows

Any help would be greatly appreciated.

Jul-19
37.1​
Aug-19
37.3​
Sep-19
36.5​
Oct-19
33.9​
Nov-19
35.5​
Dec-19
35.5​
Jan-20
36.5​
 

Attachments

  • Chandoo lookup.xlsx
    15.9 KB · Views: 5
Now as Excel appears to have forked into two very different product streams here is a solution that targets the Office 365 dynamic array side of the house:
= SUMIFS( XLOOKUP( SelectedMonth, Month, SiteFigures ), State, SelectedState )

XLOOKUP
returns a complete column of data as does @rahul's INDEX/MATCH
= SUMIFS( INDEX( SiteFigures, , MATCH(SelectedMonth, Month, 0)), State, SelectedState )

[Note: I have converted the direct references to names for my own understanding as well as to provide directly comparable formulas]

@Nebu 's solution
= SUM( IF( (Month=SelectedMonth)*(State=SelectedState), SiteFigures ) )
is a conditional sum over the 2D array.

I have only just seen @bosco's solutions but would observe that, for Office 365 being a 'non-array' solution is a positive disadvantage -- other than the fact that end users are more likely to think they understand the solution.
 

Attachments

  • Conditional sum of lookup column.xlsx
    23.7 KB · Views: 8
I love this SUMPRODUCT feature

=SUMPRODUCT((Month = SelectedMonth) * (State = LEFT ($ B $ 5,3)) * (SiteFigures))

Decio
 
@Decio
What you have used is the fact that SUMPRODUCT is one of a limited number of functions that causes the parameters to be evaluated correctly as arrays without CSE. Something that should always have always worked is
= SUM( (Month = SelectedMonth) * (State = LEFT(SelectedState,3)) * (SiteFigures) )
If you love SUMPRODUCT, you should really love dynamic array formulae!

BTW, the reason LEFT(SelectedState,3) did not appear in my formulae was that I decided it makes no sense to combine data "VIC" with annotation "target". I decided to permit the string "VIC" in cell B5 and, is so desired, add the "target" by applying a number format
@ "target"
 
Peter, good morning.

Yes I love dynamic matrix formula, for a sum with criteria, or small solutions for matrix formulas.

Now in the Excel 365 version there are 90% of the formulas you do not need CTRL + SHIFT + ENTER, and this results faster, has been corrected in this version.

I also like the way you use formulas, in the management, which in some cases are also matrix in the previous version

Hugs

Decio
 
Back
Top