=SUMIFS Help

[zone709]

Hi this first one works right.

=(SUMIFS($BZ$6:$BZ$77,$B$6:$B$77,"Carpenter")+SUMIFS($CD$6:$CD$77,$B$6:$B$77,"Carpenter"))/(BZ82+CD82)

This is doing the same, but I needed it from a different sheet. Its returning #DIV/0! and cant figure out why.

=(SUMIFS('Trade Templete'!$C$3:$C$68,'Trade Templete'!$B$3:$B$68,"Carpenter")+SUMIFS('Trade Templete'!$D$3:$D$68,'Trade Templete'!$B$3:$B$68,"Carpenter"))/(C73+D73)

 

[dreid1011]

Are C73 and D73 also on sheet 'Trade Templete' or are the on the sheet with the formula?

 

[zone709]

o that's it yes they are. This is why I come here when I'm stuck and need a second brain to help me. I just forgot about that. Soon as I read your reply I was like ooooooooooooooo. Gonna try now

 

[dreid1011]

Haha, yeah. I do the same thing sometimes.

 

[zone709]

Works thanks for the help

 

[dreid1011]

You're welcome.

 

[zone709]

hi one last thing I ran into. Want to see if you would know.

=(SUMIFS('Trade Templete'!$F$3:$F$68,'Trade Templete'!$B$3:$B$68,"Carpenter")+SUMIFS('Trade Templete'!$G$3:$G$68,'Trade Templete'!$B$3:$B$68,"Carpenter"))/('Trade Templete'!H73)

This works right, but if there is 0 on return I get #DIV/0!. I rather get 0 or nothing like blank. Possible?

 

[dreid1011]

You could wrap the entire thing in IFERROR like this:

=IFERROR((SUMIFS('Trade Templete'!$F$3:$F$68,'Trade Templete'!$B$3:$B$68,"Carpenter")+SUMIFS('Trade Templete'!$G$3:$G$68,'Trade Templete'!$B$3:$B$68,"Carpenter"))/('Trade Templete'!H73),0)

 

[zone709]

You could wrap the entire thing in IFERROR like this:

=IFERROR((SUMIFS('Trade Templete'!$F$3:$F$68,'Trade Templete'!$B$3:$B$68,"Carpenter")+SUMIFS('Trade Templete'!$G$3:$G$68,'Trade Templete'!$B$3:$B$68,"Carpenter"))/('Trade Templete'!H73),0)

Thanks for the help. I didnt think of using this maybe because it was returning div rather then error. thanks

 

[dreid1011]

You're welcome. Basically any result you get from a formula with #xxx ! is an error.