Excel Query

[GDW]

I need to sum the subtotals of a number of columns . Some of these subtotals are negative and as such I do not want to include these in the grand total. I need a formulae that will only add up the subtotals with a value greater than zero. Can anyone help me with this?

 

[granny]

Make a second column next to the subtotals. In the first cell of that column use the IF function with the logical test being (subtotal<0), the value_if_true parameter being 0 and the vaule_if_false parameter being the subtotal cell. Then drag-copy that cell down the length of the subtotals column and sum that column.

For instance, if your subtotals are in column C and the first one is C5 then your "positive subtotals" column formula in D5 will be:

=IF(C5<0,0,C5)

If the value in C5 is less than 0 then the result of that formula will be 0, if the value of C5 is 0 or greater then the result in D5 will be the same as C5.

Give it a try, it should become clearer in practice

 

[xane]

Can't you set up a series of "hidden" subtotals that take the existing ones and only return zero or a positive number, then total them instead ?

 

[kanonfodda]

you can use sumif:

=SUMIF(cell range,">0")

Look it up in excel help for more info, should do what you want though

/edit: typo

 

[granny]

Ahhh cunning kanon, cheers, learn summat new every day

 

[GDW]

Thanks guys

 

[jaba]

Damn you lot are clever! stoppit!!

 

[Jupitus]

This one was far too easy for me to bother with.









Actually, I'm gutted that someone beat me to it

 

[kanonfodda]

np, GDW, anytime

glad to be of service granny

Actually, I'm gutted that someone beat me to it

I'll let you do the next one mate