Sumifs With Index/Match Column

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

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

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

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

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

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

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

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.

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.

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.