CODEWITHSUNDEEP
arraysexcelexcel-formula
Peter Frey
Peter Frey

Reputation: 365

Sumproduct with array condition

I would like to use one sumproduct formula that relies on an array of conditions of cellvalue. So basically I want to sum something when one of the entries of some cells where found before.

I can make the array of conditions work but not by cell values. In example.

Let A1 = "a", A2 = "b", B1 = 1, B2 = 2

Then

C1 = Sumproduct((A1:A100={"a"."b"})*(B1:B100)) = 3

works fine, but 

C2 = Sumproduct((A1:A100=A1:A2)*(B1:B100)) = ERROR

Is there any way to put {"a"."b"} into a cell or an set of cells?

Greetings and Thanks for your help, Peter

Disclaimer: I know I could simply write: 

C2 = Sumproduct((A1:A100=A1)*(B1:B100)) + Sumproduct((A1:A100=A2)*(B1:B100))

But I would like to have a solution that is still nice to handle if 10+ conditions are on the list.

Upvotes: 2

Views: 689

Answers (2)

user4039065
user4039065

Reputation:

Use TRANSPOSE to make the cyclic calculation think of A1:A2 as being in a different order (... direction?) than A1:A100 and B1:B100.

=SUMPRODUCT((A1:A100=TRANSPOSE(A1:A2))*(B1:B100))

This formula is a true array formula and requires CSE.

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26640

Use COUNTIF:

=SUMPRODUCT(COUNTIF(A1:A2,A1:A100)*B1:B100)

Upvotes: 2

Related Questions