CODEWITHSUNDEEP
excelexcel-formulasumifs
Jose
Jose

Reputation: 5

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])

Upvotes: 0

Views: 111

Answers (1)

Lisa
Lisa

Reputation: 552

=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])

Upvotes: 0

Related Questions