sum negative and positive values seperately on a specific day

**h1981** · 06-27-2011, 08:52 AM

Hi,

Please help me with this.
I have a spreadsheet which looks like this. The dataset has been sorted by Customer ID.

Date----------customer ID---------profit/(loss)
01/01/11-------123-----------------------(30)
02/01/11-------123------------------------10
02/01/11-------123------------------------20
02/01/11--------456----------------------(40)
04/01/11-------456-----------------------30
04/01/11--------567-----------------------(50)

I want to aggrgate profits and losses seperately on a specific date. That is I need to arrange the above dataset in the following way.

Date---------------Profit-----------------Loss
01/01/11----------------------------------(30)
02/01/11-----------30-------------------(40)
04/01/11-----------30-------------------(50)

Pls pls help me

Thank you........

**NBVC** · 06-27-2011, 08:59 AM

Use SUMPRODUCT

First create your list of unique dates.

Then for Profits use something like:

=SUMPRODUCT(--('Sheet1'!$A$1:$A$100=A2),--('Sheet1'!$B$1:$B$100>0),'Sheet1'!$B$1:$B$100)

=SUMPRODUCT(--('Sheet1'!$A$1:$A$100=A2),--('Sheet1'!$B$1:$B$100<0),'Sheet1'!$B$1:$B$100)

Where Sheet1A1:B100 holds the database, And A2 on the current active sheet contains first unique date to check.

**Roger Govier** · 06-27-2011, 09:13 AM

Hi

With date in E2:E4, Profit in F1 and Loss in G1
enter the following in F2
=SUMIFS($C$2:$C$7,$A$2:$A$7,$E2,$C$2:$C$7,">0")

and in G2
=SUMIFS($C$2:$C$7,$A$2:$A$7,$E2,$C$2:$C$7,"<0")

Copy down as required

ooops!!!
Just noticed OP is using XL2003 so that won't work.