Share via

Sumifs not returning a value

Anonymous
2015-02-06T16:52:25+00:00

Someone out there smarter than me please help.  I have racked my brain on this for a couple hours and see no reason I get no results returned.  The bottom formula is what I am using, but even for testing purposes I separated out the below portion that has all the matching criteria and I still get no results.  I checked to make sure the cell in column C equals A29 and checked that the cell in column G is greater than 0.  I all checks out.  Last thing I did was remove the Iferror and there is no error.  Columns A, C, & G are general format and J is number. 

=SUMIFS('Post Formatted'!$J$2:$J$1500,'Post Formatted'!$C$2:$C$1500,$A29,'Post Formatted'!$G$2:$G$1500,">0")

=IFERROR(IF(OR($A29=0,$A29=""),"",IF(OR(IF($K$1=$Z$3,SUMIFS('Post Formatted'!$J$2:$J$1500,'Post Formatted'!$C$2:$C$1500,$A29,'Post Formatted'!$G$2:$G$1500,">0",'Post Formatted'!$K$2:$K$1500,$Z$3)),IF($K$1=$Z$4,SUMIFS('Post Formatted'!$J$2:$J$1500,'Post Formatted'!$C$2:$C$1500,$A29,'Post Formatted'!$G$2:$G$1500,">0",'Post Formatted'!$K$2:$K$1500,$Z$4)),IF($K$1=$Z$5,SUMIFS('Post Formatted'!$J$2:$J$1500,'Post Formatted'!$C$2:$C$1500,$A29,'Post Formatted'!$G$2:$G$1500,">0",'Post Formatted'!$K$2:$K$1500,$Z$5))),SUMIFS('Post Formatted'!$J$2:$J$1500,'Post Formatted'!$C$2:$C$1500,$A29,'Post Formatted'!$G$2:$G$1500,">0",'Post Formatted'!$K$2:$K$1500,$K$1),IF(AND($K$1="ALL",SUMIFS('Post Formatted'!$J$2:$J$1500,'Post Formatted'!$C$2:$C$1500,$A29,'Post Formatted'!$G$2:$G$1500,">0")),SUMIFS('Post Formatted'!$J$2:$J$1500,'Post Formatted'!$C$2:$C$1500,$A29,'Post Formatted'!$G$2:$G$1500,">0"),""))),"")

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-02-07T02:34:58+00:00

    But the key here is that it did come back as false even though you say something that looks like a number shows up in the cell and the cell is formatted as General.

    Is the entry left aligned or right aligned?  If it is left aligned and you haven't set the cell up to be left justified, then it's text.

    You might try adding * 1 to the formula in one of those cells and again using the =IsNumber() test against it to see if it comes back as true or not.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2015-02-06T19:15:27+00:00

    Thanks, it came back as false.  There is a number in the cell and it is formatted general though.  Should I check something else?

    0 comments
  3. Anonymous
    2015-02-06T17:57:48+00:00

    Difficult to evaluate this formula without the data but I guess it is correct as you see the desired results.

    Things you can test:

    =ISNUMBER(G2) or any other cell in G:G

    Reduce the ranges to a short range for testing, like A2:A5.

    Use Formula, Audting, Evaluate to see how Excel works it out.

    0 comments
  4. Anonymous
    2015-02-06T17:50:27+00:00

    The entire column is formatted "general".  Could it be the formula I am using in that column G? 

    =IFERROR(IF($A4="","",IF(INDEX(Redtail!$L$2:$L$4000,MATCH(1,INDEX((Redtail!$P$2:$P$4000=A4)*(Redtail!$H$2:$H$4000=B4),,),0),1)="Child",INDEX(Redtail!$N$2:$N$4000,MATCH(1,INDEX((Redtail!$P$2:$P$4000=A4)*(Redtail!$H$2:$H$4000=B4),,),0),1),"")),"")

    0 comments
  5. Anonymous
    2015-02-06T17:12:34+00:00

    Hi,

    Your test formula is working for me.

    The only thing I could find is that if your data in G is stored as text, SUMIF returns 0.

    0 comments