Add values based on multiple criteria

[myersl]

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

 

[Mike H]

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]

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]

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

 

[Mike H]

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