using INDEX and MACTH in a SUMIF formula

**moneypennie21** · 12-18-2014, 06:46 AM

Hi I have a spreadsheet which has data on one tab and lookups on another tab. I want to create a dynamic sumif formula so when I change the week number in one cell on the lookup sheet the sumif automatically looks for that week in the data and totals it. I also need to use the staff members name as a criteria. The tab. I have attached an example of the table and the data (sheet 1 and 2). I basically want the dynamic sum if in cell B8 to say if you see this header in the table on the data sheet and this area manager in column D on the data sheet then add everything up. I have tried to incorporate INDEX and MATCH to make this work but I can't get it to show a result. PLEASE HELP!

**sweep** · 12-18-2014, 09:06 AM

Hello,

You could use something like this

=SUMIF(Sheet2!$D$4:$D$30,Sheet1!$A8,OFFSET(Sheet2!A4:A30,0,MATCH(B$7,Sheet2!$3:$3,0)-1))

in B8. However, there are currently logical differences between the contents of B7 and the apparently matching cell in Sheet2 row 3. I imagine that this is because there are hard carriage returns, or other such non-printing characters present.

**moneypennie21** · 12-18-2014, 09:38 AM

HI there,

thanks or the response I have tried this and it just returns an N/A value. Not sure what you mean by logical differences in the cells?

Thanks

**moneypennie21** · 12-18-2014, 10:05 AM

This is the formula I have been trying to make work

=SUMIFS(INDEX(table1,,MATCH(A$7,Sheet2!$A$3:$AE$3,0)),Sheet2!$D$4:$D$30,$A8)

**sweep** · 12-18-2014, 10:44 AM

I would expect this

="Week"&" "&D2&" "&"Qualifying" when D2=50 to match cell T3 on sheet2.

ie, =Sheet1!B7=Sheet2!T3 should return TRUE, but actual returns FALSE.

Once this issue is resolved, the formula I supplied will work.

**moneypennie21** · 12-18-2014, 10:53 AM

Hi yes that does work you are right. However the reason there was a formula in there, (="Week"&" "&D2&" "&"Qualifying") is because I just wanted to select a week number from a drop down and for the column header to automatically update. is there any way around this?

**sweep** · 12-18-2014, 11:17 AM

I don't think I'm explaining myself too well - sorry.

If you take the formula ="Week"&" "&D2&" "&"Qualifying" when D2 contains 50, it should (and does) return "Week 50 Qualifying"

So, if everything is working as expected, =Match(Sheet1!B7,Sheet2!3:3,0) to return 20, because the 20th cell in row 3:3 contains "Week 50 Qualifying". It doesn't however, because what's actually there could be described like this:

="Week 50"&CHAR(10)&"Qualifying"&" "

The extra space at the end, and the forced carriage return make it not match. Fix the column headers in sheet 2 to make the formulas work.

**martindwilson** · 12-18-2014, 11:21 AM

i cant get the 2 to match so i used in b8
=SUMIF(Sheet2!$D$4:$D$30,$A8,INDEX(Sheet2!$H$4:$AE$30,0,MATCH("*"&$B$6&"*",Sheet2!$H$1:$AE$1,0)))
in b9
=SUMIF(Sheet2!$D$4:$D$30,$A8,INDEX(Sheet2!$H$4:$AE$30,0,MATCH("*"&$B$6&"*",Sheet2!$H$1:$AE$1,0)+1))
in b10
=SUMIF(Sheet2!$D$4:$D$30,$A8,INDEX(Sheet2!$H$4:$AE$30,0,MATCH("*"&$B$6&"*",Sheet2!$H$1:$AE$1,0)+2))

**moneypennie21** · 12-18-2014, 11:30 AM

Thank you sweep. I get it now! I have fixed the headers and this works perfectly. I am so grateful for your help!!! If you had a minute I would really like to understand how the OFFSET part of this formula works as I am not entirely sure and I think I would like to use this again and be able to explain it to someone else.

**martindwilson** · 12-18-2014, 11:37 AM

hardly any need for offset index is usually sufficient

**moneypennie21** · 12-18-2014, 11:41 AM

Thanks Martindwilson but I have made it work and am nervous to change it now!

**sweep** · 12-18-2014, 12:01 PM

OFFSET(Sheet2!A4:A30,0,MATCH(B$7,Sheet2!$3:$3,0)-1))

Sheet2!A4:30 is the origin for the offset - imagine that range selected.

The second part of the arguements ",0,MATCH(B$7,Sheet2!$3:$3,0)-1" effectively "moves" the selected range down zero rows and across "MATCH(B$7,Sheet2!$3:$3,0)-1" columns

Martin is correct that OFFSET is inefficient. You could use

=SUMIF(Sheet2!$D$4:$D$30,Sheet1!$A8,INDEX(Sheet2!$A$4:$AE$4,MATCH(B$7,Sheet2!$3:$3,0)):INDEX(Sheet2!$A$30:$AE$30,MATCH(B$7,Sheet2!$3:$3,0)))

I used OFFSET in this case as I thought it might be more intuitive to apply.

**moneypennie21** · 12-18-2014, 12:16 PM

Thank you both!. I have made it work using both versions of this formula. Again though as I am a bit of a thickie with INDEX could someone explain to me what it means in the context of this formula if any one has the time?

using INDEX and MACTH in a SUMIF formula

LinkBack

Thread Tools

Rate This Thread

Display

using INDEX and MACTH in a SUMIF formula

Re: using INDEX and MACTH in a SUMIF formula

Re: using INDEX and MACTH in a SUMIF formula

Re: using INDEX and MACTH in a SUMIF formula

Re: using INDEX and MACTH in a SUMIF formula

Re: using INDEX and MACTH in a SUMIF formula

Re: using INDEX and MACTH in a SUMIF formula

Re: using INDEX and MACTH in a SUMIF formula

Re: using INDEX and MACTH in a SUMIF formula

Re: using INDEX and MACTH in a SUMIF formula

Re: using INDEX and MACTH in a SUMIF formula

Re: using INDEX and MACTH in a SUMIF formula

Re: using INDEX and MACTH in a SUMIF formula

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] SumIf & Index formula

Macth, Index or Vlookup?

[SOLVED] Index Indirect macth

sumif or index formula help

Formula to help match 2 cell items and tell me if 1 has a macth or not

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions