Excel 2003 STDEV and SUMIF Formulas with Multiple Criteria

Hi,

I am trying to get the two formulas to work below. Basically for the first one I am trying to take the stdev of all the values in column Q that have the data in column J values listed are = "1", as well as the value in O being >19 as well as <39. If J=1, and O>19 as well as O<39, I would like it to take the stdev of all the values that fit the criteria in row Q.

The second one is very similar, except I want to sum the values if they meet the criteria.

Please Login or Register  to view this content.

Thank you so much for your help

Normally when dealing with numbers you don't use quotes (unless they are formatted as text)......and + effectively gives you an "OR", I think you want * for an "AND" so an "array formula" for the STDEV....

=STDEV(IF((J2:J6357=1)*(O2:O6357>=19)*(O2:O6357<=39),Q2:Q6357))

That needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

For a sum try SUMPRODUCT to avoid "array entry", i.e.

=SUMPRODUCT((J2:J6357=1)*(O2:O6357>=19)*(O2:O6357<=39),Q2:Q6357)