# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] Sumifs With Index/Match Column

## benno87

Hi,

I have used this technique before with a sumif formula, to use the column that matches a reference cell but I cannot get this to work on a sumifs formula. What I have used previously in a sumif formula (only 1 criteria) is below.




```
Please Login or Register  to view this content.
```


And this is the formula I currently have for my sumifs formula (multiple criteria).




```
Please Login or Register  to view this content.
```


I am using the match function to match up dates - if that gives you some sort of assistance in this.

Any help is greatly appreciated.

Cheers

----------


## NBVC

I don't have current access to test on 2007, but does this work?

=SUMIFS(INDEX('ACTUAL SALES'!$K:$AN,0,MATCH(B$20,'ACTUAL SALES'!$K$1:$AN$1,0)),'ACTUAL SALES'!$C:$C,$B$7,'ACTUAL SALES'!$H:$H,$B$6)

----------


## benno87

Thanks for your reply. I havn't tried this but I got it working.

Because the match was returning a value of 30, the index was looking at column 30 in the range K:AN. When I changed the column range from A:AN, it worked fine and perfectly.




```
Please Login or Register  to view this content.
```


I am still appreciative of your time. This place is a godsend for any technical assistance and the work you do is great!

Cheers

----------


## NBVC

That is also why I changed this part:

'ACTUAL SALES'!$1:$1

To:

'ACTUAL SALES'!$K$1:$AN$1

----------


## jcaynes

Thanks so much for sharing this - it was very helpful!

----------


## rjedw1

Hi I have a similar but slightly different problem and can not get my head around index and match - I have a data validation to chose a month - Jan=1 ect

I want to sum if on a fixed range but sum the nth column - eg if I select Jan I want the 1st column Dec the 12th - this will make some dynamic tables on my dashboard - there are a number of other selection criteria I choose to select data which is why I am using the sumif

The selected cell is in D6 of the current sheet - the data table (just the data no descriptions is in another tab - "this year" and the range is $i$7:$t:500

The first part of the sumif works as a concatenation of the site and the measure gives a unique value

so my formula is =sumif(This Year'!$A$6:$A$366,Engine!$B70,*HELP*   and I want the variable bit to return the sumrange i7:i500 if period 1 to t7:t500 in period 12 - any help is mutch appreciated and may help me seal a temp to perm position

----------


## jcaynes

Good morning, rjedw1.  This sounds similar to functions I run on dashboards that automatically populate the most recent 24 months for YoY comps.  The calculations are performed in the headers of the rows (months) and then the sumifs are used under the month headers to qualify the results.  This is a sample:




```
Please Login or Register  to view this content.
```


For this code, I'm looking up a month first, to the column header of the spreadsheet, 0 indicated exact match.  Once the header is matched, then I lay out further criteria for matching, which for me is call volume statistics.  I have also used this to manage other data, not just call volumes.  For more specific help, you may be best served by creating a new post (tag-on help requests are rarely answered) and attach a sample of the spreadsheet to it, or include the code so that people can get a really good idea of what you're doing and can better help you.

Unedited code: 


```
Please Login or Register  to view this content.
```


BV is reference the source data and BLUE VAN is referencing the "dashboard".  

I hope this is helpful to you!

Jan

----------


## rjedw1

Jan many thanks for this I will try it out as always good to learn new things.

I ended up using the following ...

=SUMIF('This Year'!$A$6:$A$366,Engine!$B70,INDEX('This Year'!$I$6:$T$366,0,Engine!$D$67))

where the a6:a366 contains the concatenation of site and metric, b70 is the concatenation derrived from the site selection from a drop down box - then the simple index worked with the D67 reference driven by the user selection of the period - which then drives a number.

As a first time user - impressed by getting a solution on the same day - thanks again.

Rob

----------


## jcaynes

Rob - glad it helped!  Often solutions are provided within a day, unless it is very complex or an oddity.

----------


## rjedw1

Hi again - could not find out how to post a new topic and searching was too vague

I am trying to do a custom number format that has zero decimal places if the number is >100 and <-100 - so between -100 and 100 i want to display 2 decimal places otherwise zero

I got the first part fro Chandoo and following the logic tried this - but it did not work - any help once again much appreciated

[<100]_(#,##0.00_);[<-100]_(#,##0);_(#,##0)  the first condition works - any positive number>100 has 1DP - the problem is with negative

----------


## yearwood6

Hi,

I need to make the formula below work for sumif or sumifs if possible.

SUM(INDEX($C$4:$FB$15,MATCH($A24,$A$4:$A$15,0),MATCH($B24&C$22,$C$1:$FB$1,0)))

C4:FB15 is the sum range, A24 is the division name, A4:A15 is the list of divisions with eg Div 1 appearing in that list multiple times, B24 is sales & C22 is the week number, C1:FB1 is the range of eg salesweek01, salesweek02 etc.

My problem is the formula only gives me the sum of the first value instead of returning the total for that division.

Any help is greatly appreciated.

----------


## benno87

Hi
On first glance, you are only using a sum, not a sumif/s. Then use an index/match to identify which column to sum (use 0 as the row reference). Im on my phone but I think that may be all the help you need.

----------


## benno87

Hi
On first glance, you are only using a sum, not a sumif/s. Then use an index/match to identify which column to sum (use 0 as the row reference). Im on my phone but I think that may be all the help you need.

----------

