Excel formula - help needed!
 

Hi,

My spreadsheet contains 3 columns.
A - product code (numerical 1 - 10000)
B - product description
C - price

I need a formula to achieve the following...
- any product codes (A) between eg 1-499 i want to add the amount (C)
for each of the products in this range.

In other words I want the formula to lookup column A, and any number
found here that's between 1 - 499 add the corresponding value in column
C.

Hope that makes sense.

Any suggestion greatly appreciated.

Cheers,

B1888

Excel formula - help needed!
 

Hi

One way
=SUMIF(A:A,"<500",C:C)

--
Regards

Roger Govier


"b1888" wrote in message
oups.com...
Hi,

My spreadsheet contains 3 columns.
A - product code (numerical 1 - 10000)
B - product description
C - price

I need a formula to achieve the following...
- any product codes (A) between eg 1-499 i want to add the amount (C)
for each of the products in this range.

In other words I want the formula to lookup column A, and any number
found here that's between 1 - 499 add the corresponding value in
column
C.

Hope that makes sense.

Any suggestion greatly appreciated.

Cheers,

B1888

Excel formula - help needed!
 

Hi Roger,

thats great thanks!

just one further question - if I now want to do the same with anything
between 500 - 999 how do I tell the formula to look between 500 - 999
rather than just <1000 as I want to exclude anything lower than 500.

Thanks again,

B


Roger Govier wrote:

Hi

One way
=SUMIF(A:A,"<500",C:C)

--
Regards

Roger Govier

Excel formula - help needed!
 

=SUMIF(A:A,"<100",C:C)-SUMIF(A:A,"<500",C:C)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"b1888" wrote in message
ups.com...
Hi Roger,

thats great thanks!

just one further question - if I now want to do the same with anything
between 500 - 999 how do I tell the formula to look between 500 - 999
rather than just <1000 as I want to exclude anything lower than 500.

Thanks again,

B


Roger Govier wrote:

Hi

One way
=SUMIF(A:A,"<500",C:C)

--
Regards

Roger Govier

Excel formula - help needed!
 

Hi

You need to use 2 Sumif's taking away the values included in the lower
section

=SUMIF(A:A,"<1000",C:C)-SUMIF(A:A,"<500",C:C)


--
Regards

Roger Govier


"b1888" wrote in message
ups.com...
Hi Roger,

thats great thanks!

just one further question - if I now want to do the same with anything
between 500 - 999 how do I tell the formula to look between 500 - 999
rather than just <1000 as I want to exclude anything lower than 500.

Thanks again,

B


Roger Govier wrote:

Hi

One way
=SUMIF(A:A,"<500",C:C)

--
Regards

Roger Govier

Excel formula - help needed!
 

oops

=SUMIF(A:A,"<1000",C:C)-SUMIF(A:A,"<500",C:C)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
=SUMIF(A:A,"<100",C:C)-SUMIF(A:A,"<500",C:C)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"b1888" wrote in message
ups.com...
Hi Roger,

thats great thanks!

just one further question - if I now want to do the same with anything
between 500 - 999 how do I tell the formula to look between 500 - 999
rather than just <1000 as I want to exclude anything lower than 500.

Thanks again,

B


Roger Govier wrote:

Hi

One way
=SUMIF(A:A,"<500",C:C)

--
Regards

Roger Govier

Excel formula - help needed!
 

Bob,
that'll do the trick.

cheers,

B

Bob Phillips wrote:

=SUMIF(A:A,"<100",C:C)-SUMIF(A:A,"<500",C:C)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Excel formula - help needed!
 

cheers Roger


Roger Govier wrote:

Hi

You need to use 2 Sumif's taking away the values included in the lower
section

=SUMIF(A:A,"<1000",C:C)-SUMIF(A:A,"<500",C:C)


--
Regards

Roger Govier