I have many named ranges Racks is a table with many blank values =SUMIF(RowNbr, "11", Racks) does not work. It returns only the first value found Does not matter if on the same sheet or another sheet Does not work with =SUMIF(N4:N145, "11", O4:AA145) either Does not matter if criteria is enclosed in quotes or not =COUNTIF(RowNbr,11) returns 4, the correct value =SUMIF(RowNbr, "11", RowNbr) works and returns 44 =SUM(Racks) returns 272 which is the correct value
Further investigation shows it only sums first column in sum_range
Hello, Thank you for filing the bug. Please send us a sample document, as this makes it easier for us to verify the bug. I have set the bug's status to 'NEEDINFO', so please do change it back to 'UNCONFIRMED' once you have attached a document. (Please note that the attachment will be public, remove any sensitive information before attaching it.) How can I eliminate confidential data from a sample document? https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F Thank you
Created attachment 123852 [details] File showing SumIf Error further investigation shows that only the first column of a multi column sum_range is summed
you can't have an 'array' for the range where the criteria is applied and a 'matrix' for the range with the sum values. Both ranges must have the same dimensions! For your purpose you need a matrix function (ctrl+shift+enter) =SUM(IF(RowNbr=R4; Racks) ) or =SUM(IF($C$4:$C$145=R4;$D$4:$P$145)) apply with ctrl+shift+enter