Lookup totals by the months going across the spreadsheet

[Kelli Webb]

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​

 

[Nebu]

Hi:

May be this?

Thanks

 

[rahulshewale1]

Hi @Kelli Webb ,

or ,

Cell B6 VIC Target=SUMIFS(INDEX(Target!$C$3:$I$11,,MATCH(Sheet1!$B$3,Target!$C$2:$I$2,0)),Target!$B$3:$B$11,LEFT(Sheet1!B5,FIND("Target",B5)-2))

Regard
Rahul Shewale

 

[bosco_yip]

Another 2 non-array formulas option :

=SUM(SUMIF(Target!B3:B11,LEFT(B5,3),INDEX(Target!C3:I11,0,MATCH(B3,Target!C2:I2,0))))

or

=SUMPRODUCT((Target!B3:B11=LEFT(B5,3))*(Target!C2:I2=B3),Target!C3:I11)

Regards
Bosco

 

[Peter Bartholomew]

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.

 

[deciog]

I love this SUMPRODUCT feature

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

Decio

 

[Peter Bartholomew]

@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"

 

[deciog]

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

 

[Kelli Webb]

Thank you to All, this took a bit to get my head around but it worked thank goodness!

Cheers!