Search code examples
excelexcel-formulasumifs

SUMIF with Multiple Criteria only Returns Results for Single Criteria

This should be an easy fix...I hope. The following formula seems to be correct but only returns the correct percentage for the first criteria and not both. Any ideas as to why? Any help would be greatly appreciated!

=SUMIF(Table4[Preferred],{"test 1","test 2"},Table4[rate])/SUM(Table4[rate])

Solution

  • =SUMIF(Table4[Preferred],{"test 1","test 2"},Table4[rate])/SUM(Table4[rate]) gives the result {25%, 40%} in your case. You only see the first number of the array in a single cell.

    To sum rate based on "Preferred" being either "test 1" or "test 2", you can use the formula

    = SUMIF(Table4[Preferred],"test 1",Table4[rate])/SUM(Table4[rate])
+ SUMIF(Table4[Preferred],"test 2",Table4[rate])/SUM(Table4[rate])

    Or, you can also wrap the entire SUMIF function in the SUM function

    =SUM(SUMIF(Table4[Preferred],{"test 1","test 2"},Table4[rate]))/SUM(Table4[rate])