sumifs help!

[jmaillard]

Hi I have this list and i would like to formula that would return:

- the sum of >0 numbers for "op"

- the sum of <0 numbers for "op"

I hope someone can come up with a witty formula! look forward to reading you.


date amount check category

1/20/2011 (42,345.56) op

1/6/2011 (41,229.89) op

1/18/2011 (17,637.90) op

1/7/2011 (16,666.00) 1476 bs

1/3/2011 (8,000.00) bs

1/3/2011 (6,497.60) op

1/26/2011 (4,898.00) op

1/10/2011 (3,960.00) 1480 op

1/21/2011 (3,375.00) 1489 op

1/25/2011 (2,577.99) 1482 op

1/12/2011 (2,500.00) op

1/3/2011 (610.17) op

1/14/2011 (500.00) op

1/10/2011 (405.67) 1481 op

1/10/2011 (320.00) 1478 op

1/12/2011 (250.00) 1477 op

1/18/2011 (228.71) op

1/3/2011 (160.00) 1472 op

1/3/2011 (158.24) op

1/14/2011 (150.00) bs

1/13/2011 (144.44) 1468 op

1/18/2011 (112.86) op

1/3/2011 (104.56) op

1/18/2011 (100.14) op

1/31/2011 (100.00) bs

1/18/2011 (100.00) op

1/7/2011 (93.19) op

1/21/2011 (93.19) op

1/25/2011 (81.14) 1485 op

1/18/2011 (75.00) op

1/10/2011 (65.22) op

1/27/2011 (61.96) op

1/19/2011 8,962.83 op

1/28/2011 8,972.40 op

1/11/2011 9,789.32 op

1/25/2011 12,550.53 op

1/14/2011 18,247.18 op

1/7/2011 25,000.00 bs

1/6/2011 26,139.91 bs

 

[Luke M]

=SUMPRODUCT(--(C2:C100="op"),--(B2:B100>0),B2:B100)


and


=SUMPRODUCT(--(C2:C100="op"),--(B2:B100<0),B2:B100)