SUMIF - Compound Condition

M

Mike McLellan

I want to be able to add the contents of selected cells within column C only
when the contents of the cells in Column A meet condition 1 and the contents
of the cells in column B meet condition 2. For example, if column A contains
Status (Completed or Not Completed), column B contains an implementation date
and column C contains the number of printers to be deployed, then I want to
be able to derive the total number of printers deployed where the status is
'Completed' and the implementation date falls within a specified week.

Can anyone suggest how I might best achieve this?
 
B

Bob Phillips

=SUMPRODUCT(--(A2:A200="Completed"),--(B2:B200>=--"2005-01-09"),--(B2:B200<=
--"2005-01-13"),C2:C200)

as an example

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
M

Mike McLellan

Bob,

Thanks - a great help. A couple of follow up questions:

1. What is the significance of the '--' in the formula?
2. If I simply wanted to count the number of non Blank cells in a column
based upon 2 similar conditions, what would I need to do?

Thanks for your help

Mike
 
B

Bob Phillips

Mike McLellan said:
Bob,

Thanks - a great help. A couple of follow up questions:

1. What is the significance of the '--' in the formula?
Click to expand...

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html

2. If I simply wanted to count the number of non Blank cells in a column
based upon 2 similar conditions, what would I need to do?
Click to expand...

Do you mean the number of items that meet the conditions? If so,

=SUMPRODUCT(--(A2:A200="Completed"),--(B2:B200>=--"2005-01-09"),--(B2:B200<=
--"2005-01-13"))

That is exclude the part that has no condition, the ,C2:C200, which is a
summing part of the formula.
 
Top