# Lookup totals by the months going across the spreadsheet

#### Kelli Webb

##### Member
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

• 15.9 KB Views: 5

Hi:

May be this?

Thanks

#### Attachments

• 16.4 KB Views: 9

#### rahulshewale1

##### Active Member
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

##### Excel Ninja
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

##### Well-Known Member
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

• 23.7 KB Views: 8

#### deciog

##### Active Member
I love this SUMPRODUCT feature

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

Decio

#### Peter Bartholomew

##### Well-Known Member
@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

##### Active Member
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

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

Cheers!