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?

Question

Hi all

Basically I have a table 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 for

week 1 --> 2

week 2 --> 5

week 3 --> 6

week 6 --> 4

week 7 --> 1

Anyone have any idea how to do this? I am a bit mift... Thanks for any help it's much appreciated.

Answer 1

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.

Answer 2

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)