fitzchivalry
New Member
- Joined
- Mar 23, 2015
- Messages
- 33
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:
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:
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!
<tbody>
</tbody>
<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.
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:
Code:
=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:
Code:
=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.
Last edited: