SUMIF Formula Assistance

[PoggiPJ]

I use the following formula to sum amounts from two ranges.
If H12 contains a "Y", then add to V12 the amount in the UnitPriceColumn in any row where the PartNoColumn matches the part number in D12.

=IF(UPPER(H12) = "Y",V12+SUMIF(PartNoColumn,D12,UnitPriceColumn),0)

This currently works, but it adds the amount in UnitPriceColumn any time PartNoColumn matches D12.

I would like to add a second conditional test to the formula where the UnitPriceColumn amount is only added when UnitPriceColumn matches D12, AND another, SubYNColumn = "Y". I am not sure how to add this second condition.

Note: I don't think it matters, but UnitPriceColumn, PartNumberColumn, and SubYNColumn all refer to columns in a different range from H12, V12, and D12.

 

[Aladin Akyurek]

Try...

=IF(UPPER(H12) = "Y",V12+
   SUMPRODUCT(--(PartNoColumn=D12),--(SubYNColumn = "Y"),UnitPriceColumn),
   0)

 

[PoggiPJ]

That worked perfectly, thank you!

Hopefully you have a moment to answer a couple of followup questions.

1. What does the "--" do in the formula
2. I thought that the Sumproduct would just multiply the two arrays. In this example it is also performing a conditional test as well?

 

[Aladin Akyurek]

That worked perfectly, thank you!

Hopefully you have a moment to answer a couple of followup questions.

1. What does the "--" do in the formula

They effect coercion from TRUE/FALSE to 1/0. See below...

2. I thought that the Sumproduct would just multiply the two arrays. In this example it is also performing a conditional test as well?

Right. SumProduct indeed multiplies its terms and adds up the results of multiplications...

[1] SUMPRODUCT(--(PartNoColumn=D12),--(SubYNColumn = "Y"),UnitPriceColumn)

is equivalent to either of the following which must be confirmed with control+shift+enter:

[2] SUM((PartNoColumn=D12)*(SubYNColumn = "Y")*UnitPriceColumn)

[3] SUM(IF(PartNoColumn=D12,IF(SubYNColumn = "Y",UnitPriceColumn)))

As we know, terms like

PartNoColumn=D12

evaluate to a set of TRUE/FALSE values. If such a set can be coerced into a set of 1/0's, a formula with SumProduct which requires numbers becomes possible. This coercion can be achieved in different ways like in:

--(PartNoColumn=D12),...

(PartNoColumn=D12)+0,...

(PartNoColumn=D12)*...

Here is a link on the matter:

http://www.mrexcel.com/forum/showthread.php?t=202204

 

[PoggiPJ]

This is incredibly helpful to me. Thank you for taking the time and trouble to elaborate.