value error in sumifs function

Question

I created Sumifs function in which sum range is C5:F7 and criteria range 1 is B5:B7 and criteria 1 is J5 and criteria range 2 is C4:F4 and Criteria 2 is K4. The above is shown in figure below;

Any help would be highly appreciated.

Answer 1

When you use SUMIFS, each criteria range must have the same number of rows and columns as the sum range or it returns a #VALUE! error. In your case the sum range is 3Rx4C, the second range is 3Rx1C and the third range is 1Rx4C.

But what result were you hoping to achieve, because even if it worked the criteria applied to your formula would just return the same table you already have? For example, the SUM of the values where both the "code"=13 AND the date is Jan-19 is 25.

Perhaps you were after sums by row and column, which you can achieve with simple SUM formulas.

Answer 2

Hi,

By inserting the     C$4:F$4,K$4   in  your formula,  you are trying to ensure that the sum is for the "Jan-19" header only.

Since the month-Year are the same in the data area as well as in the target area

the formula does not require this second parameter (criteria-range2 and criteria2)

The result shall come correct even if you use only    =SUMIFS(C5:C7,$B5:$B7,$J5)

Give it a try.

Hope this helps.

Answer 3

You may calculate this using SUMPRODUCT.

Enter below formula in cell K5 and copy down and to the right:

=SUMPRODUCT($C$5:$F$7*($C$4:$F$4=K$4)*($B$5:$B$7=$J5))

But as already mentioned, this will replicate the same values only.

Regards,

Amit Tandon

www.excelanytime.com

Answer 4

Thanks Murray,

I actually want to sum data from multiple worksheets in same workbook on the basis of multiple criteria and i am using the formula =sumproduct(sumifs(indirect and i am using named sheet range in this formula.

You can check above formula in figure 2 below;

In different sheets data is arranged with row and column headings and row and column heading are changed in couple of data sheets. You can see the data sheets tables in below figure 3. I am attaching the workbook containing data sheets and summary sheet with formula mentioned above but i am getting the value error. 

Any help will be highly appreciated.

Regards,

Yasir

Answer 5

Slightly difficult to tell even with the images, but with the formula the problem (or at least one problem) remains the same, and that is that your criteria for sumifs have both different numbers of rows and columns from each other as well as from the sum range.

The sum range C6:Q51 is 46Rx15C, the first criteria range C4:Q4 is 1Rx15C, and the second criteria range B6:B24 is 19Rx1C.

I can't see that the SUMPRODUCT is doing anything in this context. An array formula will likely achieve your aim. Here's a stab at what might work:

=SUM((INDIRECT("'"&SheetsMaterialCon&"'!C4:Q4")=D$3)*(INDIRECT("'"&SheetsMaterialCon&"'!B6:B24")=$C4) *INDIRECT("'"&SheetsMaterialCon&"'!C6:Q51"))

Enter an array formula using CTRL+SHIFT+ENTER, not just enter. i.e. hold down CTRL+SHIFT and press the enter key. IF you do it correctly the formula will appear surrounded by braces { and }. You cannot enter these manually.

If you still can't get it to work and the data are not too confidential you're welcome to save it to OneDrive or similar and post a link and I will have a look at it for you.