Sum Row with standard condition

[ShafiqqAziz]

Hi guys,

Need some help with my formula.

Here's an example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Quantity 1[/TD]
[TD]Quantity 2[/TD]
[TD]Quantity 3[/TD]
[TD]Quantity 4[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]-1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD]1[/TD]
[TD]-1[/TD]
[TD]-2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]-3[/TD]
[/TR]
[TR]
[TD]Item 4[/TD]
[TD]-1[/TD]
[TD]-1[/TD]
[TD]2[/TD]
[TD]-3[/TD]
[/TR]
</tbody>[/TABLE]

Here's example's answer :

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Quantity Negative[/TD]
[TD]Quantity Positive[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]-1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD]-3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD]-3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Item 4[/TD]
[TD]-5[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

What i want to make is to sum the negative and positive pair with item.
Tried this formula SUMPRODUCT((A2:A5=A2)*(B2:E5>0)*(B2:E5)) it returned #VALUE ! error.
Tried following formulas still no answer
1. https://www.extendoffice.com/docume...ultiple-columns-based-on-single-criteria.html
2. https://support.office.com/en-us/ar...nditions-e610ae0f-4d27-480c-9119-eb644f1e847e
3. https://www.extendoffice.com/documents/excel/4097-excel-sum-based-on-column-and-row-criteria.html

Can you guys help me out? Thank you

 

[Legacy 143009]

For positives:
=SUMIF(B2:E2,">="&0)
For negatives:
=SUMIF(B2:E2,"<"&0)

 

[Aladin Akyurek]

If A2:A5 is exactly repeated in the answer layout, a SumIf formula Flashbond suggests will suffice.

 

[ShafiqqAziz]

Yes, A2:A5 will be the same. Can you give me example for your suggestion?

 

[ShafiqqAziz]

I must insert this formula for each rows right? I have 1000+ rows of data, and i cannot do that for each of the rows.

 

[Aladin Akyurek]

I must insert this formula for each rows right? I have 1000+ rows of data, and i cannot do that for each of the rows.

No. Try to learn some very elementary things about the spreadsheets. One is: entering a formula in a cell and copying that formula to other cells...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td][/td][td]Quantity 1[/td][td]Quantity 2[/td][td]Quantity 3[/td][td]Quantity 4[/td][td]Quantity Negative[/td][td]Quantity Positive[/td][/tr]
[tr][td]

2​

[/td][td]Item 1[/td][td]

-1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

-1​

[/td][td]

6​

[/td][/tr]
[tr][td]

3​

[/td][td]Item 2[/td][td]

1​

[/td][td]

-1​

[/td][td]

-2​

[/td][td]

3​

[/td][td]

-3​

[/td][td]

4​

[/td][/tr]
[tr][td]

4​

[/td][td]Item 3[/td][td]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

-3​

[/td][td]

-3​

[/td][td]

4​

[/td][/tr]
[tr][td]

5​

[/td][td]Item 4[/td][td]

-1​

[/td][td]

-1​

[/td][td]

2​

[/td][td]

-3​

[/td][td]

-5​

[/td][td]

2​

[/td][/tr]
[/table]

In F2 enter and copy down:

=SUMIF($B2:$E2,"<0",$B2:$E2)

In G2 enter and copy down:

=SUMIF($B2:$E2,">0",$B2:$E2)

 

[ShafiqqAziz]

Okay, got it! Thank you very much!

 

[Aladin Akyurek]

Okay, got it! Thank you very much!

You are welcome.