How can I add up negative sales for each week of the year? (I'm almost there I think...)

[fitzchivalry]

Hi

I want to add up a column of transactions, including only negative amounts, and display this by week no and month no.

I've already found out how to add up all sales (as below), but I can't figure out how to change it to only include negative amounts.

The formulas below add up everything. Having read about combining SUMPRODUCT and SUMIF my best guesses for Negative Sales for each week were:

=SUMPRODUCT((WEEKNUM($A:$A+0)=$D2)*1,(SUMIF($B:$B,"<0",$B:$B)

This returns #VALUE!, and I've tried using the Formula Evaluator to step through the results but I don't really know what I'm doing...

And for each month:

=SUMPRODUCT((WEEKNUM($A:$A+0)=$E2)*1,$B:$B*($B:$B="<0"))

This returns £0.00.

I'm still trying to crack this one, but have been stuck for a while... (despite the complexity of the formulas I'm using, I still consider myself a novice). If you read this within a couple of hours of this post, I'm likely still online working on this, help very much appreciated!

Date (A)	Sale Amount (B)	Week No (J)	WeekNum (H)
01/01/2015	10	1	=(WEEKNUM($A2)=$J2)
13/01/2015	-5	2	=(WEEKNUM($A3)=$J3)
05/02/2015	25	3	=(WEEKNUM($A4)=$J4)
20/02/2015	-5	4	Etc...
04/03/2015	40	5
21/04/2015	-20	6
09/05/2015	15	7
10/06/2015	25	8
20/06/2015	25	Etc...

<tbody>
</tbody>

Today (C)	Week No (D)	Week Total (E)	Month No (F)	Month Total (G)	Year No (H)	Year Total (I)
=SUMIF($A:$A, TODAY(),$B:$B)	1	=SUMPRODUCT((WEEKNUM($A:$A+0)=$D2)*1,$B:$B)	1	=SUMPRODUCT((WEEKNUM($A:$A+0)=F2)*1,$B:$B)	2013	=SUMPRODUCT((YEAR($A:$A)=$H2)*($B:$B))
	2	=SUMPRODUCT((WEEKNUM($A:$A+0)=$D3)*1,$B:$B)	2	=SUMPRODUCT((WEEKNUM($A:$A+0)=F3)*1,$B:$B)	2014	=SUMPRODUCT((YEAR($A:$A)=$H3)*($B:$B))
	3	=SUMPRODUCT((WEEKNUM($A:$A+0)=$D4)*1,$B:$B)	3	=SUMPRODUCT((WEEKNUM($A:$A+0)=F4)*1,$B:$B)	2015	=SUMPRODUCT((YEAR($A:$A)=$H4)*($B:$B))
	4	Etc...	4	Etc...		Etc...
	5		5
	6		6
	7		7
	8		8
	9		9
	10		10
	11		11
	12		12
	13
	14
	15
	16
	17
	18
	Etc...

<tbody>
</tbody>

P.S: Thanks Aladdin (for Month Total) and Barry Houdini (for Week Total; I had trouble with WEEKNUM) for previous posts that have enabled me to get this far - I'm talking about historical posts on this forum that I found, not answers to questions I myself posted.

 

[fitzchivalry]

And for each month:

=SUMPRODUCT((WEEKNUM($A:$A+0)=$E2)*1,$B:$B*($B:$B="<0"))

This returns £0.00.

This should say:

=SUMPRODUCT((TransConfirmedTrans="<0")*(TransDate=$C3)*(TransConfirmedTrans))

Not that it works...

 

[fitzchivalry]

I've just the answers, through Scott Huish's post here (thank you ) http://www.mrexcel.com/forum/excel-...egative-positives-conditions.html#post4135424

Answer are, for month:

=ABS(SUMPRODUCT(--(MONTH($A:$A)=$F2),--($B:$B<0),$B:$B))

And for by week:

=ABS(SUMPRODUCT(--(WEEKNUM($A:$A+0)=$F2)*1,--($B:$B<0),$B:$B))

I need to work out the number for each week and month too (the count) of negative transactions, but I'll work on that tomorrow.. for day it's

=COUNTIFS($B:$B,"<0",$A:$A,TODAY())