SUMIFS and INDEX MATCH #VALUE error

[rpconcannnon]

Hello Excel Experts:

Both expressions below use INDEX MATCH. The SUMIF formula is working properly but I'm getting a #VALUE error messages with the SUMIFS formula.

=SUMIF('CP Data'!B:B,Summary!E1,INDEX('CP Data'!1:1,MATCH(Summary!$B4,'CP Data'!1:1,0))) --------------> correct value
.....................................=SUMIFS(INDEX('CP Data'!1:1,MATCH(Summary!$B4,'CP Data'!1:1,0)),'CP Data'!A:A,Summary!D1,'CP Data'!H:H,Summary!A4) -------------> #VALUE

#VALUE indicates a data type error, but all of the expressions appear to evaluate properly including the Index Match which evaluates to the same value in both formulas: 'CP Data'!$K$1 so I'm not clear where the error is. Thanks for any help!

Robert

[:) Sixthsense :)]

Surely it will not work

In the below Index() output you Passed 1 row which consists multiple columns
INDEX('CP Data'!1:1,

Will result the placement in which column the match is found.
MATCH(Summary!$B4,'CP Data'!1:1,0)

Index(ResultingRng,Row,Column)

I don't know what is the use of match here because your output range consist only one row and using match in row part is the error part.
INDEX('CP Data'!1:1,MATCH(Summary!$B4,'CP Data'!1:1,0))

This 'CP Data'!A:A will have 1048576 cell values but this 'CP Data'!1:1 will output 16384 values so the passed ranges are not equal in size

[ELeGault]

I am guessing your Index is causing this error (Focusing on a single row)... Post a sample data and write out what you expect this formula to evaluate and the expected results of your given data.

[rpconcannnon]

To clarify, the intent was to provide a dynamic reference to the column within the SUMIFS formula in case of a column position change. As noted below, the cause of the #VALUE error was the INDEX formula. It was only looking at the first row as opposed to the entire column. Here's the corrected version with 'CP Data'!$A:$AZ referencing the entire column and MATCH returning the specific column within the 'CP Data'!$A:$AZ array. Thanks very much!

=SUMIFS(INDEX('CP Data'!$A:$AZ,0,MATCH(Summary!$B4,'CP Data'!$1:$1,0)),'CP Data'!$B:$B,Summary!C$1,'CP Data'!$I:$I,Summary!$A4)

[Jonmo1]

This was actually an intersting post.
Worth explaining what was going on..

Take the original sumif that works
=SUMIF('CP Data'!B:B,Summary!E1,INDEX('CP Data'!1:1,MATCH(Summary!$B4,'CP Data'!1:1,0)))

Let's just say that the MATCH function finds B4 in G1.
G1 is th 7th cell in 1:1
So Match returns 7
=SUMIF('CP Data'!B:B,Summary!E1,INDEX('CP Data'!1:1,7))
The 7th cell in 'CP Data'!1:1 is therefor G1

So it becomes
=SUMIF('CP Data'!B:B,Summary!E1,'CP Data'G1)

Your Sum Range and Criteria Range are not the same dimensions.

Normally, you want your Sum Range to be of equal size to the Criteria Range.
However, Sumif does it's best to figure out that you meant for the sum range to be same dimensions as the criteria range.
Completing the sumif as if you had provided the sum range in same demension as the criteria range.
How or Why it does that, I'm not exaclty sure.


That works in Sumif
But it does NOT work in SumifS

Sumifs actually requires the Sum Range to be of equal dimensions to all the Criteria Ranges.
That is why it returned #Value! for the Sumifs.


The solution as you found was to make the Index Range use the same number of rows as your criteria ranges.