Forum Discussion
Why is my SUMIF formula not working?
In this sheet, there is a hidden formula that just displays "<Unnamed #"> if the user puts a number with no category name. My sumif formula works for categories that have the same name or single names but not if it's "<Unnamed #>". Each <Unnamed #>" is a unique number and I have the match function set to EXACT (0). Any thoughts as to why the sumif is summing everything (and not $15 in this case)?
=IFERROR(IF($B7="","<Unnamed 3>",$B7),"<Unnamed 3>")
=SUMIF($A$5:$A$29,INDEX($A$5:$A$29,MATCH($F$7,$A$5:$A$29,0),1),$C$5:$C$29)
Just figured it out. It was reading the "<" as a value. Unfortunately, the VALUETOTEXT doesn't like working as the RANGE argument in SUMIF but I just changed all the <Unnamed #> to '<Unnamed #> and it works now with: SUMIF($A$5:$A$29,INDEX($A$5:$A$29,MATCH($F$5:$F$29,$A$5:$A$29,0),1),$C$5:$C$29)
1 Reply
Just figured it out. It was reading the "<" as a value. Unfortunately, the VALUETOTEXT doesn't like working as the RANGE argument in SUMIF but I just changed all the <Unnamed #> to '<Unnamed #> and it works now with: SUMIF($A$5:$A$29,INDEX($A$5:$A$29,MATCH($F$5:$F$29,$A$5:$A$29,0),1),$C$5:$C$29)