Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - problem with a Sum If (I think)
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - problem with a Sum If (I think)
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) "Scoobs" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - problem with a Sum If (I think)
Perhaps
=SUM(INDIRECT("B1" & ":" & "B" & A5)) Changing the value of A5 will change the number of rows summed. Mike "Scoobs" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - problem with a Sum If (I think)
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" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - problem with a Sum If (I think)
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 "Mike H" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - problem with a Sum If (I think)
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. "Mike H" wrote: 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" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000 - problem with a Sum If (I think)
So sorry - Stephen - it was your sumproduct function that worked - sorry to
be so dizzy..... "Stephen" wrote: 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) "Scoobs" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2000 problem | Excel Worksheet Functions | |||
IF Function Problem Excel 2000 | Excel Worksheet Functions | |||
PRINTING PROBLEM W/EXCEL 2000 | Excel Discussion (Misc queries) | |||
Excel 2000 PageBreak problem | Excel Discussion (Misc queries) | |||
Problem with Excel 2000 | Excel Discussion (Misc queries) |