Are you stuck on Excel or Google Sheets?

How can I do a sumif formula on visible cells only

So im trying to use a sumif formula, but i need it to not look for a range for one specific criteria but instead look for what one specific field says. Example Below =sum(sumifs(QuantityMC,TeamsMC,A6,TypeMC,B2)) The above formula works as on the specific MC sheet an entire column is dedicated for "TypeMC". I want to be able to delete this column and have "TypeMC" to be a single field as it will be the same for every item on that sheet but may differ on a different sheet.

I need explanation for the excel formula below... =IF(ISERROR(SUMIFS(INDIRECT("'"&BE$6&"'!"&"$G:$G"),INDIRECT("'"&BE$6&"'!"&"$R:$R"),$A39,INDIRECT("'"&BE$6&"'!"&"$F:$F"),"<>52711",INDIRECT("'"&BE$6&"'!"&"$C:$C"),10)),0,SUMIFS(INDIRECT("'"&BE$6&"'!"&"$G:$G"),INDIRECT("'"&BE$6&"'!"&"$R:$R"),$A39,INDIRECT("'"&BE$6&"'!"&"$F:$F"),"<>52711",INDIRECT("'"&BE$6&"'!"&"$C:$C"),10))+IF(ISERROR(SUMIFS(INDIRECT("'"&BE$4&"'!"&"$A$30:$EO$30"),INDIRECT("'"&BE$4&"'!"&"$A$4:$EO$4"),BE$5,INDIRECT("'"&BE$4&"'!"&"$A$5:$EO$5"),$A39)),0,SUMIFS(INDIRECT("'"&BE$4&"'!"&"$A$30:$EO$30"),INDIRECT("'"&BE$4&"'!"&"$A$4:$EO$4"),BE$5,INDIRECT("'"&BE$4&"'!"&"$A$5:$EO$5"),$A39))+IF(ISERROR(SUMIF(INDIRECT("'"&BE$6&" "&$A$31&"'!"&"H:H"),$A39,(INDIRECT("'"&BE$6&" "&$A$31&"'!"&"I:I")))),0,SUMIF(INDIRECT("'"&BE$6&" "&$A$31&"'!"&"H:H"),$A39,(INDIRECT("'"&BE$6&" "&$A$31&"'!"&"I:I"))))

I am trying to figure out how to use a sumif on SUBSTITUTE(A2,"*","~*") where A2 is a formula with 3371-4