Please help..Anyone know how can I make a chart to sum all the data of each week only if values are greater than 0?

Basically I have a t开发者_如何学Pythonable like this:

Week   |   Value
1      |      2
2      |      3
2      |     -1
2      |      1
2      |      1
3      |      4
3      |      2    
6      |      4
7      |      1

I need a chart to show the sum of the values for each week only if the value is greater than 0. so

• week 1 --> 2
• week 2 --> 5
• week 3 --> 6
• week 6 --> 4
• week 7 --> 1

Anyone have any idea how to do this?

If you have Excel 2007/2010, you can use the newer function SUMIFS as below: =SUMIFS(Week_Values,Week_Numbers,This_Week2,Week_Values,">0")

This is two criteria, so you either need an intermediate column, or an array formula.

Option 1 - Extra Column:

Add a 3rd column, which has =IF(This_Value>0,This_Value,0) (where This_Value is the cell for that row with the value in it) and then do your SUMIF on the week number to sum up the new column; OR

Option 2 - Array Formula:

For each week number as This_Week in its own cell: =SUM(IF((Week_Numbers=This_Week)*(Week_Values>0),Week_Values,0)) which you must finish up with Ctrl-Shift-Enter instead of just Enter. More on Array Formulas at cpearson.com and Microsoft.

When you have more than one condition you could use SUMPRODUCT (simpler than Array formulas). In this case suppose you have the data you propose on the cells A1:B10 (with column names Weeks and Values in A1:B1). then put in D2 the number of the week for which you want the sum (say: 2)

Then:

=SUMPRODUCT(($A$2:$A$10=$D2)*($B$2:$B$10>0)*$B$2:$B$10)

would do.

PS: be aware that you must esclude the column names from the selection of SUMPRODUCT (i.e. do not use $A$1:$A$10 in the above formula)