To sum satisfying two conditions in excel

This is a bit of a hack solution, but it will always work:

• Create a cell for each condition you want to test. It should return a "1" if the condition is met, and "0" otherwise.
  
• Create a new cell (lets call it flag). This cell should return either 1 or 0, depending on the type of overall conditional you want. For example, if you want to include the value if any one of a number of conditions is met (an "OR" test), then the flag should be set to 1 if the sum of the individual conditionals is greater than zero. If you want to add the value only when all the conditions are met (an "AND" test), then you'd return 1 if the sum of the individual conditionals equals the number of conditions you're testing.
  
• Finally, create a new cell that multiplies the value you want to test by the flag cell. This cell will equal the value if the condition is met (i.e., the flag cell = 1); otherwise, it will be equal to 0.

An example will make this clearer. Here is your original data, with a few extra columns that I've added to do some conditional testing:

• Column F is the logical test for the first condition. Its formula is "=IF(B1=DATEVALUE("03/31/09"),1,0)"
  
• Column G is the second conditional. Its formula is "=IF(A1>19411,1,0)"
  
• Column H is the "flag" conditional. Its formula is "=IF(SUM(F1:G1) > 0,1,0)"
  
• Column I is the conditional value. It's equal to column H*E.

The sum of Column I is the conditional total you're interested in. Note that you could add as many conditions as you want to make this more complex.

To do an "AND" test, set column H (the flag) to "=IF(SUM(F1:G1) = 2,1,0)." Now column H no equals 1 only when all the conditions are met.



If you don't like the look of extra columns (or you're just a glutton for punishment), you could put everything in one huge "If" statement. But, that can be very hard to debug, so I prefer to to build it up step by step, (Alternatively, you could just hide the extra columns.)

Hope this helps!