Is there a way of condensing this Formula(Sumifs,Indirect,Match)?

[Coyotex3]

This formula works as intended but I'm looking for ways to make the formula's shorter and easier to understand if possible. Are there any shorter alternatives to this formula?

=IFERROR(SUMIF(INDIRECT("'[Source1.xlsx]"&$B$1&"'!C"&MATCH("Person",INDIRECT("'[Source1.xlsx]"&$B$1&"'!2:2",TRUE),0),FALSE),A3,INDIRECT("'[Source1.xlsx]"&$B$1&"'!C"&MATCH("Total per Person",INDIRECT("'[Source1.xlsx]"&$B$1&"'!2:2",TRUE),0),FALSE)),0)*N3

 

[Gimics]

Hey Coyote - more context about your data sets might be helpful.

It's a strange formula for sure - it looks like the MATCH portion of the formulas are looking up words ("Person" or "Total per Person" within a row (2:2, specifically), which means the value being returned is actually a number for a column.

However, the results of that MATCH formula are being placed in as a row reference (ex. ...!C"&MATCH... results in something like ...!C3 being the reference).

So the fact that the match lookup is finding a column value and filling it as a row reference kinda suggests the whole formula is a bit overkill - it looks like your just doing a single cell lookup and probably should just be using an INDEX formula instead?

But maybe it would all make more sense with a couple of screenshots or description of the tables your formula are referencing.

 

[Coyotex3]

I think the original intent of this formula was to lookup "Person" within the column headers, in a different workbook and then return a value. I just came across this formula and I've had a hard time deciphering it. I will try to see if I can provide an example.

Thank you.