SUMIF Formula Assistance

PoggiPJ

Active Member
Joined
Mar 25, 2008
Messages
330
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.
Code:
=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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Try...
Code:
=IF(UPPER(H12) = "Y",V12+
   SUMPRODUCT(--(PartNoColumn=D12),--(SubYNColumn = "Y"),UnitPriceColumn),
   0)
 

PoggiPJ

Active Member
Joined
Mar 25, 2008
Messages
330
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Active Member
Joined
Mar 25, 2008
Messages
330
This is incredibly helpful to me. Thank you for taking the time and trouble to elaborate.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top