# Sumifs is giving me 0 even though it clearly is >0



## xaikus506 (Dec 21, 2022)

Hi I have a problem, I don't know what happen sometimes it gives me a correct value and sometimes 0 in the number of error values

Dashboard.xlsxABCDE6Name of AnalystDate of ProcessNumber of ErrorsSubtaskApprover's Name7 Azuelo, Chad01/11/20222CoReq: 22-01-10Mendoza, LeslieDashboard ProtCell FormulasRangeFormulaA6:E7A6=VSTACK({"Name of Analyst","Date of Process","Number of Errors","Subtask","Approver's Name"},
IFERROR(IF($B$2="",{"","","","",""},
IF(OR($B$2="all",$B$2="All",$B$2="ALL"),
CHOOSECOLS(SORT(SORT(FILTER('Error Logs Consolidated'!B:H,
(ISERROR(SEARCH("Analyst*",'Error Logs Consolidated'!D:D))*
('Error Logs Consolidated'!D:D<>0))*
(ISERROR(SEARCH("0",'Error Logs Consolidated'!B:B)))),2,1),3,1),{3,2,5,4,1}),
IF(AND($B$2<>"",$B$3="Name of Analyst"),
CHOOSECOLS(SORT(SORT(FILTER('Error Logs Consolidated'!B:H,
(ISERROR(SEARCH("Analyst",'Error Logs Consolidated'!D:D))*(ISNUMBER(SEARCH($B$2,'Error Logs Consolidated'!D:D)*
(ISERROR(SEARCH("0",'Error Logs Consolidated'!B:B))))))),2,1),3,1),{3,2,5,4,1}),
IF(AND($B$2<>"",$B$3="Date of Process"),
CHOOSECOLS(SORT(SORT(FILTER('Error Logs Consolidated'!B:I,
(ISNUMBER(SEARCH($B$2,'Error Logs Consolidated'!I:I)*
(ISERROR(SEARCH("0",'Error Logs Consolidated'!B:B)))))),2,1),3,1),{3,2,5,4,1}),
IF(AND($B$2<>"",$B$3="Subtasks"),
CHOOSECOLS(SORT(SORT(FILTER('Error Logs Consolidated'!B:H,
(ISNUMBER(SEARCH($B$2,'Error Logs Consolidated'!E:E)*
(ISERROR(SEARCH("0",'Error Logs Consolidated'!B:B)))))),2,1),3,1),{3,2,5,4,1}),"")
)))),{"","","","",""}))Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueA1:E1,A4:E1048576,A2:A3,C2:E3Celldoes not contain a blank value textNO

Dashboard.xlsxGHI6Name of AnalystSubtaskNumber of Errors7 Azuelo, ChadCoReq: 22-01-100Dashboard ProtCell FormulasRangeFormulaG7:H7G7=UNIQUE(CHOOSECOLS(FILTER(A:D,(A:A<>"")*(A:A<>A2)*(A:A<>A3)*(A:A<>A6)),{1,4}))I7I7=SUMIFS(C:C,A:A,BYROW(G7:INDEX(G:G,COUNTA(G:G)+5),LAMBDA(x,x)),D:D,BYROW(H7:INDEX(H:H,COUNTA(H:H)+5),LAMBDA(y,y)))Dynamic array formulas.Named RangesNameRefers ToCells'Dashboard Prot'!_FilterDatabase='Dashboard Prot'!$A$6:$D$301I7, G7Cells with Conditional FormattingCellConditionCell FormatStop If TrueG1:I3,G6:I1048576,G4:H5Celldoes not contain a blank value textNO


----------



## bebo021999 (Dec 21, 2022)

Sometime formula gives wrong result, due to some reasons.
One of them is "Circular References" 
First check if any cell in sheet had refered to itself.


----------



## StephenCrump (Dec 21, 2022)

C7 looks like "2" (text) rather than 2 (numeric).

If your values in column C are text, then your SUMIFS() in column I will return 0.


----------



## xaikus506 (Dec 21, 2022)

StephenCrump said:


> C7 looks like "2" (text) rather than 2 (numeric).
> 
> If your values in column C are text, then your SUMIFS() in column I will return 0.


Hi Stephen, Thanks for the response. I am not sure if I did change the data type in Cell C correctly to number but it still does not work.






but this works correctly as intended


----------



## RoryA (Dec 21, 2022)

With the data in your first picture, select C7 and click the Left align button on the ribbon to remove any manual alignment. Does the number stay on the left? If so, it's text, not a number, and you need to look at the source data.


----------



## xaikus506 (Dec 21, 2022)

Hi @RoryA, Thanks for the response. I managed to find the culprit of the error. the cell in the raw data was in text format. Thank you for your assistance.


----------

