Sum based on multiple criteria in different columns

[Rockforce]

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.

 

[Aladin Akyurek]

It seems the invoice ID's are irrelevant for unique and TRUE corresponds 100%...

Thus:

=SUMIFS(C2:C7,B2:B7,TRUE)

 

[Rockforce]

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.

 

[Aladin Akyurek]

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))

 

[Rockforce]

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)

 

[Aladin Akyurek]

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))​

 

[Rockforce]

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))​

Lifesaver, thank you.

 

[Aladin Akyurek]

Lifesaver, thank you.

You are welcome.