Help With Sumproduct and multiple criteria. (Solved)

[rsd007]

Hello,

I am trying to work with Sumproduct formula with multiple criteria but is not working can someone help me with this.
i am trying to calculate total value of each product with some color good and imperfect separate.
Thanks

 

[John Jairo V]

Hi, @rsd007!

I leave two options in the file. Check it! Blessings!

 

[rsd007]

Hello,
Thanks for quick reply, Problem is it should only multiply and add only White and Grey items and rest should be zero.

 

[bosco_yip]

Try to add an If function as in:

F2 formula copied down:

=IF(SUMPRODUCT((A2="White")+(A2="Grey")),SUMPRODUCT((($A$2:A2="White")+($A$2:A2="Grey"))*($B$2:B2=B2)*$D$2:D2*ABS($E$2:E2)),0)

 

[John Jairo V]

Hi to both!

Ok... see the attachment again. Blessings!

 

[rsd007]

Thankyou Both
bosco_yip
&
John Jairo V

I wanted to keep the Cell Blank if conditions not exists John formula insert - . Both solution work

 

[bosco_yip]

Then, try this:

=IF((A2="White")+(A2="Grey"),SUMPRODUCT((($A$2:A2="White")+($A$2:A2="Grey"))*($B$2:B2=B2)*$D$2:D2*ABS($E$2:E2)),"")

 

[rsd007]

Thankyou for the help and the support received.
Happy New year

 

[rsd007]

Hello,

New problem found some time formula work some time I get only #Value ( have tried all suggestion but get same result )

 

[bosco_yip]

Maybe,

=IF(OR(D8={"Buy";"Bought"}),SUMPRODUCT($H$8:H8,ABS($J$8:J8),--($E$8:E8=E8),($D$8:D8="Buy")+($D$8:D8="Bought")),"")

 

[rsd007]

Hi,

Work on this sheet when try to run on main sheet dose not work will get Cell on line 8 as blank which is correct but rest is #VALUE

 

[rsd007]

Solved