Multiple Ranges & Conditions

I have a worksheet that I am trying to solve the sum of 1 column of dollar amounts, but I have 2 ranges that can affect whether or not it is included in the sum.

ECP Standard $1,199.00
ECS Committed $449.00
ECS Committed $449.00
MCS Committed $298.00
MCS Committed $298.00
MCS Committed $298.00
ECS GOA $150.00
ECS Committed $430.00
ECS Committed $430.00
MCS Standard $349.00
ECP Committed $1,000.00
ECS Standard $599.00
ECP Standard $1,000.00
ECP Standard $1,000.00
ECP Standard $1,000.00
ECS Standard $599.00
TCS Committed $175.00

So I want to be able to come up with 3 different sums based on A = either EC/MC/TC and then if B = Committed. Not sure how to solve for SUM when using multiple ranges, and conditions...

IF(RANGE $A2:$A34="MC*" AND RANGE $B2:$B34="Comm*",SUM($C2:$C34)

Hope that makes sense....

Any suggestions would be greatly appreciated.

Rich -

Smile like you mean it!

If you note in my posting, there are different versions, ECP, ECS etc, that may or may not be committed, I want to be able to pick certain items to sum or not sum.

IF A meets criteria B, then SUM in the entire range.

I hope that clarifies - I can match the exact text if need be, just thought it might be easier to use a wildcard MC* for example.

R

That works beautifully - thinking in terms of SQL Searching...can I ask - what is the purpose of the -- in the formula?

Originally Posted by NBVC

it serves to coerce the array of Trues and Falses returned by each condition to array of 1's and 0's respectively, so that the arithmetic of the Sumproduct() function can be performed as designed.

Thanks - I had no idea - I have learned much today - thanks to all who have taken moment to respond and contribute - I hope to someday be a help to someone as you have been for me today.

- R