Excel formula - help needed!

B

b1888

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
 
B

b1888

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
 
B

Bob Phillips

=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 said:
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
Click to expand...
 
R

Roger Govier

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 said:
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
Click to expand...
 
B

Bob Phillips

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 said:
=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)
Click to expand...
 
Top