Excel 2000 - problem with a Sum If (I think)

[Scoobs]

Hello
Can anyone please help me.
I am trying to create a function that will sum up a column of numbers if
another cell in another column equals a particular value.

I have 2 columns with values in them - e.g.

A1 Column A Column B
A2 1 10
A3 2 20
A4 3 30
A5 4 40
etc.

example:
if A5 = 4, then add up all in coumn B

I have tried numerous things and nothing seems to work.

I would be grateful for any help that can be given.

 

[Stephen]

What if A5 is not equal to 4? This formula will display nothing in that
case:
=IF(A5=4,SUM(A2:A5,"")
Adjust the range A2:A5 as needed. Is that what you want?

Or do you mean you want the sum of those cells in column B where the
adjacent column A entry is 4? If so, try
=SUMPRODUCT(--(A2:A5=4),B2:B5)

 

[Mike H]

Perhaps

=SUM(INDIRECT("B1" & ":" & "B" & A5))

Changing the value of A5 will change the number of rows summed.

Mike

 

[Mike H]

On second thoughts perhaps you mean

=IF(A5=5,SUM(B:B),"A5 isn't 5 so I'm not going to sum column B")

Mike

 

[Scoobs]

Hey Mike & Stephen - I am so grateful for the amazingly quick replies - this
problem is for my MD - I will take your replies to him and come back to you.

Many thanks

 

[Scoobs]

Mike

Thank you for seeing beyond my explanation! - your sumproduct function
worked perfectly - thank you so much and appologies to both for not
explaining the problem fully initially.

What a fab service this is.

 

[Scoobs]

So sorry - Stephen - it was your sumproduct function that worked - sorry to
be so dizzy.....