If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add values based on multiple criteria
Hi
I am trying to add values dependant on two seperate criteria I have tried a formula which I found using excel help but it is adding the total of the whole column, in other words it is ignoring the criteria. =SUM(IF((A12:A300="Jones and Co")*(G12:G300="Ordered"),M12:M300)) Column A has customer names, I wanto add the values in column M only when the customer is a specific name and the status in column G is a specific status. I hope this makes sense. Any help would be appreciated Thanks |
#2
|
|||
|
|||
Add values based on multiple criteria
Hi,
The formula should work exactly as you describe and only sum when the criteria are met. Did you enter it as an array with CTRL+Shift+Enter and not just enter? If you do it correctly Excel will put curly brackets {} around it. You can't type these yourself. Mike "myersl" wrote: Hi I am trying to add values dependant on two seperate criteria I have tried a formula which I found using excel help but it is adding the total of the whole column, in other words it is ignoring the criteria. =SUM(IF((A12:A300="Jones and Co")*(G12:G300="Ordered"),M12:M300)) Column A has customer names, I wanto add the values in column M only when the customer is a specific name and the status in column G is a specific status. I hope this makes sense. Any help would be appreciated Thanks |
#3
|
|||
|
|||
Add values based on multiple criteria
Thanks Mike
I was doing that bit wrong !! Forgot about CTRL+Shift+Enter. Now it returns #VALUE! so I must still have something wrong, any ideas? Thanks Linda "Mike H" wrote: Hi, The formula should work exactly as you describe and only sum when the criteria are met. Did you enter it as an array with CTRL+Shift+Enter and not just enter? If you do it correctly Excel will put curly brackets {} around it. You can't type these yourself. Mike "myersl" wrote: Hi I am trying to add values dependant on two seperate criteria I have tried a formula which I found using excel help but it is adding the total of the whole column, in other words it is ignoring the criteria. =SUM(IF((A12:A300="Jones and Co")*(G12:G300="Ordered"),M12:M300)) Column A has customer names, I wanto add the values in column M only when the customer is a specific name and the status in column G is a specific status. I hope this makes sense. Any help would be appreciated Thanks |
#4
|
|||
|
|||
Add values based on multiple criteria
Hi,
I'm afraid I'm now at a loss. The only way I know of to get the value error is if the formula isn't commited correctly. With the cell selecetd the formula should look like this in the formula bar {=SUM(IF((A12:A300="Jones and Co")*(G12:G300="Ordered"),M12:M300))} With the cell still selected tap F2 and the formula should now look like this =SUM(IF((A12:A300="Jones and Co")*(G12:G300="Ordered"),M12:M300)) Note the curly brackets should be gone. Now press and hold CTRL & Shift and then tap Enter and the curly brackets should reappear. If any of the rows satisfy the criteria then you should see them summed. If none of them match (or the numbers in column M aren't really numbers) then you should see zero but not a #Value error. Mike "myersl" wrote: Thanks Mike I was doing that bit wrong !! Forgot about CTRL+Shift+Enter. Now it returns #VALUE! so I must still have something wrong, any ideas? Thanks Linda "Mike H" wrote: Hi, The formula should work exactly as you describe and only sum when the criteria are met. Did you enter it as an array with CTRL+Shift+Enter and not just enter? If you do it correctly Excel will put curly brackets {} around it. You can't type these yourself. Mike "myersl" wrote: Hi I am trying to add values dependant on two seperate criteria I have tried a formula which I found using excel help but it is adding the total of the whole column, in other words it is ignoring the criteria. =SUM(IF((A12:A300="Jones and Co")*(G12:G300="Ordered"),M12:M300)) Column A has customer names, I wanto add the values in column M only when the customer is a specific name and the status in column G is a specific status. I hope this makes sense. Any help would be appreciated Thanks |
#5
|
|||
|
|||
Add values based on multiple criteria
Hi,
I should have added I wouldn't have started with an array formula, I would have used this =SUMPRODUCT((A12:A300="Jones and Co")*(G12:G300="Ordered"),(M12:M300)) Mike "Mike H" wrote: Hi, I'm afraid I'm now at a loss. The only way I know of to get the value error is if the formula isn't commited correctly. With the cell selecetd the formula should look like this in the formula bar {=SUM(IF((A12:A300="Jones and Co")*(G12:G300="Ordered"),M12:M300))} With the cell still selected tap F2 and the formula should now look like this =SUM(IF((A12:A300="Jones and Co")*(G12:G300="Ordered"),M12:M300)) Note the curly brackets should be gone. Now press and hold CTRL & Shift and then tap Enter and the curly brackets should reappear. If any of the rows satisfy the criteria then you should see them summed. If none of them match (or the numbers in column M aren't really numbers) then you should see zero but not a #Value error. Mike "myersl" wrote: Thanks Mike I was doing that bit wrong !! Forgot about CTRL+Shift+Enter. Now it returns #VALUE! so I must still have something wrong, any ideas? Thanks Linda "Mike H" wrote: Hi, The formula should work exactly as you describe and only sum when the criteria are met. Did you enter it as an array with CTRL+Shift+Enter and not just enter? If you do it correctly Excel will put curly brackets {} around it. You can't type these yourself. Mike "myersl" wrote: Hi I am trying to add values dependant on two seperate criteria I have tried a formula which I found using excel help but it is adding the total of the whole column, in other words it is ignoring the criteria. =SUM(IF((A12:A300="Jones and Co")*(G12:G300="Ordered"),M12:M300)) Column A has customer names, I wanto add the values in column M only when the customer is a specific name and the status in column G is a specific status. I hope this makes sense. Any help would be appreciated Thanks |
Thread Tools | |
Display Modes | |
|
|