I wish to create a subtotal in a filtered list, which I know I could do using
the =sumproduct formula. However, I would like to know if it is possible to
exclude negative numbers from the sum. Is it possible?

Can you elaborate by what you mean by 'filtered list' ?
Also post the formula which you use right now using SUMPRODUCT()?

'To exclude negative numbers from a list of numbers..
=SUMIF(A:A,"0")

PS: You might need to use =SUBTOTAL() function based on the exact
requirements.

If this post helps click Yes
---------------
Jacob Skaria


"Chris waller" wrote:

I wish to create a subtotal in a filtered list, which I know I could do using
the =sumproduct formula. However, I would like to know if it is possible to
exclude negative numbers from the sum. Is it possible?

The version of Excel I am using is 2002. The formula that I am currently
using is:- =SUBTOTAL(9,$D$2:$D$10), however I have found that
=SUMPRODUCT(SUBTOTAL(9,D2:D10)) gives the same answer. In relation to the
filtered list, column A contains a list of products and I need to be able to
sum the number of products shich the formula will do, however there are
instances where there are negative numbers in the list and I need to exclude
these from the equation, whilst the list is on screen. HTH

"Jacob Skaria" wrote:

Can you elaborate by what you mean by 'filtered list' ?
Also post the formula which you use right now using SUMPRODUCT()?

'To exclude negative numbers from a list of numbers..
=SUMIF(A:A,"0")

PS: You might need to use =SUBTOTAL() function based on the exact
requirements.

If this post helps click Yes
---------------
Jacob Skaria


"Chris waller" wrote:

I wish to create a subtotal in a filtered list, which I know I could do using
the =sumproduct formula. However, I would like to know if it is possible to
exclude negative numbers from the sum. Is it possible?

Chris, try the below

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2:D10,ROW(D2:D10)-MIN(ROW(D2:D10)),0,1)),--(D2:D100),D2:D10)

If this post helps click Yes
---------------
Jacob Skaria


"Chris waller" wrote:

The version of Excel I am using is 2002. The formula that I am currently
using is:- =SUBTOTAL(9,$D$2:$D$10), however I have found that
=SUMPRODUCT(SUBTOTAL(9,D2:D10)) gives the same answer. In relation to the
filtered list, column A contains a list of products and I need to be able to
sum the number of products shich the formula will do, however there are
instances where there are negative numbers in the list and I need to exclude
these from the equation, whilst the list is on screen. HTH

"Jacob Skaria" wrote:

Can you elaborate by what you mean by 'filtered list' ?
Also post the formula which you use right now using SUMPRODUCT()?

'To exclude negative numbers from a list of numbers..
=SUMIF(A:A,"0")

PS: You might need to use =SUBTOTAL() function based on the exact
requirements.

If this post helps click Yes
---------------
Jacob Skaria


"Chris waller" wrote:

I wish to create a subtotal in a filtered list, which I know I could do using
the =sumproduct formula. However, I would like to know if it is possible to
exclude negative numbers from the sum. Is it possible?

Try...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(D2:D10,ROW(D2:D10)-ROW(D2),0,1)),--(D2:D10
0))

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Chris waller wrote:

The version of Excel I am using is 2002. The formula that I am currently
using is:- =SUBTOTAL(9,$D$2:$D$10), however I have found that
=SUMPRODUCT(SUBTOTAL(9,D2:D10)) gives the same answer. In relation to the
filtered list, column A contains a list of products and I need to be able to
sum the number of products shich the formula will do, however there are
instances where there are negative numbers in the list and I need to exclude
these from the equation, whilst the list is on screen. HTH

"Jacob Skaria" wrote:

Can you elaborate by what you mean by 'filtered list' ?
Also post the formula which you use right now using SUMPRODUCT()?

'To exclude negative numbers from a list of numbers..
=SUMIF(A:A,"0")

PS: You might need to use =SUBTOTAL() function based on the exact
requirements.

If this post helps click Yes
---------------
Jacob Skaria


"Chris waller" wrote:

I wish to create a subtotal in a filtered list, which I know I could do
using
the =sumproduct formula. However, I would like to know if it is possible
to
exclude negative numbers from the sum. Is it possible?

Jacob,

Thanks for your prompt reply. It worked a treat. Thanks once again.

"Jacob Skaria" wrote:

Chris, try the below

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D2:D10,ROW(D2:D10)-MIN(ROW(D2:D10)),0,1)),--(D2:D100),D2:D10)

If this post helps click Yes
---------------
Jacob Skaria


"Chris waller" wrote:

The version of Excel I am using is 2002. The formula that I am currently
using is:- =SUBTOTAL(9,$D$2:$D$10), however I have found that
=SUMPRODUCT(SUBTOTAL(9,D2:D10)) gives the same answer. In relation to the
filtered list, column A contains a list of products and I need to be able to
sum the number of products shich the formula will do, however there are
instances where there are negative numbers in the list and I need to exclude
these from the equation, whilst the list is on screen. HTH

"Jacob Skaria" wrote:

Can you elaborate by what you mean by 'filtered list' ?
Also post the formula which you use right now using SUMPRODUCT()?

'To exclude negative numbers from a list of numbers..
=SUMIF(A:A,"0")

PS: You might need to use =SUBTOTAL() function based on the exact
requirements.

If this post helps click Yes
---------------
Jacob Skaria


"Chris waller" wrote:

I wish to create a subtotal in a filtered list, which I know I could do using
the =sumproduct formula. However, I would like to know if it is possible to
exclude negative numbers from the sum. Is it possible?

Domenic,

Thanks for that. It worked a treat. I will pass yours and Jacob's response
on to my colleague, who was experiencing the problem. Thanks once again.

"Domenic" wrote:

Try...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(D2:D10,ROW(D2:D10)-ROW(D2),0,1)),--(D2:D10
0))

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Chris waller wrote:

The version of Excel I am using is 2002. The formula that I am currently
using is:- =SUBTOTAL(9,$D$2:$D$10), however I have found that
=SUMPRODUCT(SUBTOTAL(9,D2:D10)) gives the same answer. In relation to the
filtered list, column A contains a list of products and I need to be able to
sum the number of products shich the formula will do, however there are
instances where there are negative numbers in the list and I need to exclude
these from the equation, whilst the list is on screen. HTH

"Jacob Skaria" wrote:

Can you elaborate by what you mean by 'filtered list' ?
Also post the formula which you use right now using SUMPRODUCT()?

'To exclude negative numbers from a list of numbers..
=SUMIF(A:A,"0")

PS: You might need to use =SUBTOTAL() function based on the exact
requirements.

If this post helps click Yes
---------------
Jacob Skaria


"Chris waller" wrote:

I wish to create a subtotal in a filtered list, which I know I could do
using
the =sumproduct formula. However, I would like to know if it is possible
to
exclude negative numbers from the sum. Is it possible?