Sum based on multiple criteria in different columns

Rockforce

New Member
Joined
May 31, 2016
Messages
6
Hi, I'm trying to sum a list of values if two criteria are met in two different columns. See below for example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Invoice ID (Criteria 1)[/TD]
[TD]TRUE/FALSE (Criteria 2)[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]True[/TD]
[TD]$60[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]False[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]True[/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]False[/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]False[/TD]
[TD]$30[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]True[/TD]
[TD]$30[/TD]
[/TR]
</tbody>[/TABLE]

What I'm do is come up with a formula that will only add the amount in column C if the invoice ID (column A) is unique AND the value is "TRUE" in column B.

So for instance, the desired outcome for the formula based on the data above would be $60 + $50 + $30 = $140.

Any help would be greatly appreciated.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
It seems the invoice ID's are irrelevant for unique and TRUE corresponds 100%...

Thus:

=SUMIFS(C2:C7,B2:B7,TRUE)
 
Upvote 0
It is relevant because you could potentially add two of the exact same row, which I'm trying to avoid. For instance:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]4[/TD]
[TD]True[/TD]
[TD]$30[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]True[/TD]
[TD]$30
[/TD]
[/TR]
</tbody>[/TABLE]
I would like to avoid summing duplicate entries.
 
Upvote 0
It is relevant because you could potentially add two of the exact same row, which I'm trying to avoid. For instance:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]4[/TD]
[TD]True[/TD]
[TD]$30[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]True[/TD]
[TD]$30[/TD]
[/TR]
</tbody>[/TABLE]
I would like to avoid summing duplicate entries.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(A2:A7),IF($B2:$B7,MATCH(A2:A7,A2:A7,0))),ROW(A2:A7)-ROW(A2)+1),C2:C7))
 
Upvote 0
Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(A2:A7),IF($B2:$B7,MATCH(A2:A7,A2:A7,0))),ROW(A2:A7)-ROW(A2)+1),C2:C7))

I forgot to mention that the IDs could be alphanumeric. How should I modify the formula to account for that? (I'm assuming isnumber(), but I just don't know what to change it to)
 
Upvote 0
I forgot to mention that the IDs could be alphanumeric. How should I modify the formula to account for that? (I'm assuming isnumber(), but I just don't know what to change it to)

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(A2:A7=""),IF($B2:$B7,MATCH(A2:A7,A2:A7,0))),ROW(A2:A7)-ROW(A2)+1),C2:C7))
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,703
Members
453,748
Latest member
akhtarf3

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
Back
Top