Issue with Sumifs Function

I'm trying to do a Sumifs to find the sum of cells in a column that match criteria in two other columns (same row). I'm getting a Value error. I need to use the criteria that links the current cell in that row (so if Im in row 5, one criteria should be "=D3".

Can I not use cell references as criteria? Do the cell references have to be in a certain format?? Any help would be great!

It should be just D3, not "=D3", although that will not be the cause of the error. Do you have existing errors in the data range that the formula refers to?

Originally Posted by jason.b75

It should be just D3, not "=D3", although that will not be the cause of the error. Do you have existing errors in the data range that the formula refers to?

Ok, so if I type in D3 it should refer to that specific cell as a criteria?

No, there are no errors in the reference cells. They are formulas however, not static values. Is that an issue?

Originally Posted by Cutter

You'll get the #VALUE error if the ranges are mismatched.

Show us the formula that is causing the error.

=SUMIFS($K$3:$K$1048576,$N$3:$N$1048576,N3,$B$3:$B$1346,"=No")

Formula and static values / constants would evaluate the same.

As cutter says, mismatched ranges could be the cause, it will be easier to find the error if you post the formula you're trying.

If it uses named ranges then you would also need to provide the defenitions behind the names.

edit:- reply posted while I was typing

it should be

=SUMIFS($K$3:$K$1048576,$N$3:$N$1048576,N3,$B$3:$B$1048576,"=No")

Originally Posted by jason.b75

Formula and static values / constants would evaluate the same.

As cutter says, mismatched ranges could be the cause, it will be easier to find the error if you post the formula you're trying.

If it uses named ranges then you would also need to provide the defenitions behind the names.

edit:- reply posted while I was typing

it should be

=SUMIFS($K$3:$K$1048576,$N$3:$N$1048576,N3,$B$3:$B$1048576,"=No")

Great that worked! However, I'm getting a weird issue now. Some of the values are "summing" to an odd number of 14,552 even though they don't meet the criteria of "=No". Also odd is that they total nearly 200,000 not 14,552. Any suggestions to fix that?

Originally Posted by Ace_XL

In your forumla, $B$3:$B$1346 should be $B$3:$B$1048576 too

else
=SUMIFS($K$3:$K$1346,$N$3:$N$1346,N3,$B$3:$B$1346,"=No") if your range of data is between rows 3-1346

I got it fixed, however it still doesn't seem to be properly picking up "=No" criteria. Some things are summing that do not fit that criteria. Any suggestions?

Originally Posted by Ace_XL

Just a thought...shouldn't "=No" just read as "No" instead?

I tried that, it doesn't seem to make a difference. What is interesting is that it is summing to a seemingly abatrary number, not the actual sum of those criteria (regardless whether it pays attention to the "=No" criteria.