CODEWITHSUNDEEP
excelexcel-formula
oryan5000
oryan5000

Reputation: 91

Conditional Weighted Average using SUMPRODUCT, SUMIF, ISNUMBER

I need to write a formula that will return the weighted average WAM based on weighted average of the Balance for all Products that satisfy both of the following conditions:

  1. Product = "Direct Finance Lease"
  2. BU_Type contains the word "truck" in it

I believe a conditional SUMPRODUCT is neccassary and ISNUMBER may need to be used to satisfy the 2nd criteria. As seen in the image below, the correct output should be:

28.065 = ((26*1674611)+(30*1787893))/(1674611+1787893)

I apologize, my current formula is not anywhere near functional at this point:

=SUMPRODUCT(--($B:$B=ISNUMBER("truck"),--$A:$A="Direct Finance Lease",$C:$C,$F:$F)/SUMPRODUCT(--($B:$B=ISNUMBER("truck"),--$A:$A="Direct Finance Lease",$C:$C)

EXAMPLE

Upvotes: 0

Views: 4798

Answers (1)

SJR
SJR

Reputation: 23081

In this set up, which is slightly simplified from yours but essentially the same, I added the last column (E is just C x D) and used this formula

=SUMIFS(E2:E6,A2:A6,"B",B2:B6,"*truck*")/SUMIFS(C2:C6,A2:A6,"B",B2:B6,"*truck*")

enter image description here

If you don't want a helper column, could use this

=SUMPRODUCT((A2:A6="B")*(ISNUMBER(SEARCH("truck",B2:B6))*(C2:C6)*(D2:D6)))/SUMPRODUCT((A2:A6="B")*(ISNUMBER(SEARCH("truck",B2:B6))*(C2:C6)))

Upvotes: 1

Related Questions