SUMIFS and #values!

[csliger931]

Hi All,
I have a really dumb question that I can't seem to figure out. I have a SUMIFS formula searching a range of numbers that contain a few #values! errors. Obviously, the SUMIFS result is going to be an error (#values!) for these, and I can't use aggregate since it doesn't have SUMIFS. The easiest way to fix this would be an IFERROR statement nested in the SUMIFS formula (i.e. SUMIFS(A2:A13, B2:B13, "RED", IFERROR(A2,"")), but I'm not even sure I can do this, or where I would put the IFERROR function. Does anyone know how to have the SUMIFS formula skip "#values!" errors? Or perhaps another formula that would work better? I would rather not remove all of the #values! from my range because that is going to add alot of time to my exercise (I have multiple tabs with multiples #values! errors).

Thanks!
Chris

 

[Toadstool]

Hi CSLiger931,

Does this help?

CSLiger931.xlsx
	E	F	G	H	I
1	Animal	Data 1	Data 2		Result
2	Cat	1	6		209
3	Dog	2	7
4	Cat	#VALUE!	8
5	Dog	4	9
6	Cat	5	#VALUE!
7	Dog	100	200
8
Sheet1

Cell Formulas
Range	Formula
I2	I2	=SUMIFS($G$2:$G$8,$G$2:$G$8,"<>#VALUE!",$F$2:$F$8,"<>#VALUE!",$F$2:$F$8,">3",$E$2:$E$8,"Dog")
F4,G6	F4	=INDEX(C3:C10,"A")

 

[csliger931]

Toadstool,

I think that will work!! I will try it as soon as I get in the office, but it makes complete sense....thanks man!!!! I'll mark as solved...thanks for the help!

Chris

 

[Toadstool]

Toadstool,

I think that will work!! I will try it as soon as I get in the office, but it makes complete sense....thanks man!!!! I'll mark as solved...thanks for the help!

Chris

You're welcome!