Understanding what is happening in a formula

[RodneyC]

Rather than keep asking the same type question over and over, I’d like to ask questions about a formula, understand what is happening with it, why it’s not working and apply what I learn to future efforts. Please correct me where I'm wrong on my bullet points below:

I have this formula in cell B2 on a tab named Sheet 1:

=SUMIFS(INDEX(By Podcast!$A$2:$N$490,,2), By Podcast!$A$2:$A$490, $A2)

• It is looking at a tab named “By Podcast” in the range $A$2:$N$490 for the value in column 2 (which is B) A2-N490 is where all my data is
• It is also looking at tab By Podcast in the range $A$2:$A$490 to see if it has the value in $A2 from Sheet 1.
• This formula should sum all of the values in By Podcast column B where the value in By Podcast $A$2:$A$490 equal the value in Sheet 1 $A2

Is this correct? It’s producing a #NAME? result. When I place the formula and hit enter, a dialogue box opens as if it’s trying to find the document I’m trying to pull data from yet all the data is in the same doc as the cell with the formula.

Thanks for your patience and efforts to teach me.

 

[Fluff]

When sheets have spaces in their name you need to wrap the in single quotes like

=SUMIFS(INDEX('By Podcast'!$A$2:$N$490,,2), 'By Podcast'!$A$2:$A$490, $A2)

That said I see no reason for the index, you can simply use

=SUMIFS('By Podcast'!$B$2:$B$490, 'By Podcast'!$A$2:$A$490, $A2)

 

[RodneyC]

When sheets have spaces in their name you need to wrap the in single quotes like

=SUMIFS(INDEX('By Podcast'!$A$2:$N$490,,2), 'By Podcast'!$A$2:$A$490, $A2)

That said I see no reason for the index, you can simply use

=SUMIFS('By Podcast'!$B$2:$B$490, 'By Podcast'!$A$2:$A$490, $A2)

AWESOME! I did not know the single quote was needed when a tab name had spaces. Thank you

 

[Fluff]

You're welcome & thanks for the feedback.