CODEWITHSUNDEEP
arraysexcelsumifsmultiple-conditions
Richard Guiller Alabastro
Richard Guiller Alabastro

Reputation: 11

Sumifs 2 sets of multiple criteria

I had made some researches here before posting. I might missed something so please be nice.

Anyway, I am trying to derive the sum by having 2 sets of multiple conditions into 2 criterias.

For example,

I need the sum of dogs, fish and cats by owners Alpha and Beta.

My formula is

=Sum(Sumifs(Numberofpets,pet,{"dog","fish","cat"},Owner,{"Alpha","Beta"}))

Only the Dog has a sum. Is it possible to have 2 sets of array in sumifs?

The best alternative that I have is below.

=Sum(Sumifs(Numberofpets,pet,{"dog","fish","cat"},Owner,"Alpha")+Sumifs(Numberofpets,pet,{"dog","fish","cat"},Owner,"Beta"))

The arrays for the owners might expand in the future.

Thanks a lot!

Upvotes: 1

Views: 378

Answers (1)

cronos2546
cronos2546

Reputation: 1106

In this case, I would use a named range with the array of values as the lookup condition. 

=sumproduct(sumifs(Numberofpets,pets,choose({1,2},dogrange,ownerrange)))

Upvotes: 0

Related Questions