Spreadsheet: 120974d1315698451-how-to-use-sumifs-when-using-two-different-date-ranges-sumifsform2
Sheet: Sheet1

 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | TOTAL DD
 | evaluating row # | 32 |  |  |  | evaluating row # | 33 |  |  |  | evaluating row # | 34
 | sold at: |  |  |  |  | sold at: |  |  |  |  | BOUGHT at:
 | begin period | end period | High for Day: | Drawdown |  | begin period | end period | High for Day: | Drawdown |  | begin period | end period | Low for Day | Drawdown
Trade Record |  |  |  |  | Trade Analysis
 |  |  |  |  |  |  |  | Period | Period
trade opened time | Buy/Sell? | open price | trade closed time | close price | begin period | end period | Trade P/L | High | Low | DrawDown on Open Trades
2009-01-21 11:18:45 | Sell | 1.58671 | 2009-01-27 18:17:13 | 1.6154 | 2009-01-21 00:00:00 | 2009-01-22 00:00:00 | -25.14035400078833 | 1.6155 | 1.565 |  |  | Cell K 32:
 | Sell | 1.61415 | 2009-01-28 11:41:38 | 1.63952 | 2009-01-22 00:00:00 | 2009-01-23 00:00:00 | 0 | 1.6154 | 1.5825 |  |  | the formula result for this cell should be determened by:
 | Buy | 1.67997 | 2009-02-02 07:37:54 | 1.64976 | 2009-01-23 00:00:00 | 2009-01-24 00:00:00 | 0 | 1.6042 | 1.5748 |  |  | 1.  Begin by getting the values of [NAME] and G32  (a 24 hour period)
 |  |  |  |  | 2009-01-24 00:00:00 | 2009-01-25 00:00:00 | 0 | 1.6093 | 1.5738 |  |  | 2.  Check all rows in columns A and D to see if any of the dates are (a) > the value of F32 and (b) <  the value of G32)
 |  |  |  |  | 2009-01-25 00:00:00 | 2009-01-26 00:00:00 | 0 | 1.6151 | 1.5892 |  |  | 3.  For each row which matches the criteria of item #2 above:
 |  |  |  |  | 2009-01-26 00:00:00 | 2009-01-27 00:00:00 | 0 | 1.6483 | 1.6124 |  |  | a.   If  column B = "Sell" then  we want Column I minus Column C  (in other words, the value of the open price minus the highest price of that day, as represented in column I)
 |  |  |  |  | 2009-01-27 00:00:00 | 2009-01-28 00:00:00 | -21.89694935451837 | 1.65 | 1.6267 |  |  | b.   If  column B = "Buy" then  we want Column J minus Column C  (in other words, the value of the open price minus the lowest price of that day, as represented in columnJ)
 |  |  |  |  | 2009-01-28 00:00:00 | 2009-01-29 00:00:00 | 0 | 1.6835 | 1.637 |  |  | 4.   Add values of 3a and3b.    This is the solution for [NAME].
 |  |  |  |  | 2009-01-29 00:00:00 | 2009-01-30 00:00:00 | 0 | 1.6802 | 1.6353
 |  |  |  |  | 2009-01-30 00:00:00 | 2009-01-31 00:00:00 | -26.00089094273471 | 1.6588 | 1.6374 |  |  | Note: it is fine with me if we need to break this into two or more calculations.
 |  |  |  |  | 2009-01-31 00:00:00 | 2009-02-01 00:00:00 | 0 | 1.6857 | 1.6416
 |  |  |  |  | 2009-02-01 00:00:00 | 2009-02-02 00:00:00 | 0 | 1.7144 | 1.6685
 |  |  |  |  | 2009-02-02 00:00:00 | 2009-02-03 00:00:00 | 43.63817282821685 | 1.7291 | 1.7112
 |  |  |  |  | 2009-02-03 00:00:00 | 2009-02-04 00:00:00 | 0 | 1.7357 | 1.7095
 |  |  |  |  | 2009-02-04 00:00:00 | 2009-02-05 00:00:00 | 0 | 1.7485 | 1.6747
 |  |  |  |  | 2009-02-05 00:00:00 | 2009-02-06 00:00:00 | -26.05106776859504 | 1.684 | 1.6543
 |  |  |  |  | 2009-02-06 00:00:00 | 2009-02-07 00:00:00 | 8.489331445904954 | 1.6676 | 1.6459
 |  |  |  |  | 2009-02-07 00:00:00 | 2009-02-08 00:00:00 | 0 | 1.695 | 1.6583
 |  |  |  |  | 2009-02-08 00:00:00 | 2009-02-09 00:00:00 | 0 | 1.6696 | 1.6515
 |  |  |  |  | 2009-02-09 00:00:00 | 2009-02-10 00:00:00 | 25.18721629279811 | 1.6795 | 1.6563
 |  |  |  |  | 2009-02-10 00:00:00 | 2009-02-11 00:00:00 | 0 | 1.6832 | 1.6553
 |  |  |  |  | 2009-02-11 00:00:00 | 2009-02-12 00:00:00 | 0 | 1.6965 | 1.6709
 |  |  |  |  | 2009-02-12 00:00:00 | 2009-02-13 00:00:00 | 0 | 1.7033 | 1.6557
 |  |  |  |  | 2009-02-13 00:00:00 | 2009-02-14 00:00:00 | 0 | 1.7041 | 1.6629
 |  |  |  |  | 2009-02-14 00:00:00 | 2009-02-15 00:00:00 | 0 | 1.7028 | 1.6744
 |  |  |  |  | 2009-02-15 00:00:00 | 2009-02-16 00:00:00 | 0 | 1.6912 | 1.6544
 |  |  |  |  | 2009-02-16 00:00:00 | 2009-02-17 00:00:00 | 0 | 1.679 | 1.6562
 |  |  |  |  | 2009-02-17 00:00:00 | 2009-02-18 00:00:00 | 0 | 1.6749 | 1.6572
 |  |  |  |  | 2009-02-18 00:00:00 | 2009-02-19 00:00:00 | 0 | 1.6772 | 1.6375
 |  |  |  |  | 2009-02-19 00:00:00 | 2009-02-20 00:00:00 | 0 | 1.6599 | 1.644
 |  |  |  |  | 2009-02-20 00:00:00 | 2009-02-21 00:00:00 | 0 | 1.6682 | 1.652
 |  |  |  |  | 2009-02-21 00:00:00 | 2009-02-22 00:00:00 | 0 | 1.6686 | 1.6499
 |  |  |  |  | 2009-02-22 00:00:00 | 2009-02-23 00:00:00 | 0 | 1.6582 | 1.6236
 |  |  |  |  | 2009-02-23 00:00:00 | 2009-02-24 00:00:00 | 0 | 1.639 | 1.5968
 |  |  |  |  | 2009-02-24 00:00:00 | 2009-02-25 00:00:00 | 0 | 1.6049 | 1.5851
 |  |  |  |  | 2009-02-25 00:00:00 | 2009-02-26 00:00:00 | 0 | 1.6052 | 1.5909
 |  |  |  |  | 2009-02-26 00:00:00 | 2009-02-27 00:00:00 | -14.87501863979849 | 1.6536 | 1.5878
 |  |  |  |  | 2009-02-27 00:00:00 | 2009-02-28 00:00:00 | 0 | 1.6738 | 1.648
 |  |  |  |  | 2009-02-28 00:00:00 | 2009-03-01 00:00:00 | 0 | 1.6836 | 1.6599
 |  |  |  |  | 2009-03-01 00:00:00 | 2009-03-02 00:00:00 | 0 | 1.6743 | 1.6523
 |  |  |  |  | 2009-03-02 00:00:00 | 2009-03-03 00:00:00 | 3.348056039850561 | 1.6622 | 1.6084
 |  |  |  |  | 2009-03-03 00:00:00 | 2009-03-04 00:00:00 | 0 | 1.6382 | 1.6218
 |  |  |  |  | 2009-03-04 00:00:00 | 2009-03-05 00:00:00 | 0 | 1.6351 | 1.6181
 |  |  |  |  | 2009-03-05 00:00:00 | 2009-03-06 00:00:00 | 0 | 1.6485 | 1.6273
 |  |  |  |  | 2009-03-06 00:00:00 | 2009-03-07 00:00:00 | -6.965931818181818 | 1.6645 | 1.6385
 |  |  |  |  | 2009-03-07 00:00:00 | 2009-03-08 00:00:00 | 0 | 1.6631 | 1.6223
 |  |  |  |  | 2009-03-08 00:00:00 | 2009-03-09 00:00:00 | 20.14899500780031 | 1.6424 | 1.6258
 |  |  |  |  | 2009-03-09 00:00:00 | 2009-03-10 00:00:00 | -16.73479334257975 | 1.6441 | 1.6218
 |  |  |  |  | 2009-03-10 00:00:00 | 2009-03-11 00:00:00 | -44.81468909809335 | 1.6384 | 1.6234
 |  |  |  |  | 2009-03-11 00:00:00 | 2009-03-12 00:00:00 | 0 | 1.643 | 1.6247
 |  |  |  |  | 2009-03-12 00:00:00 | 2009-03-13 00:00:00 | 0.1320579399141631 | 1.6584 | 1.63
 |  |  |  |  | 2009-03-13 00:00:00 | 2009-03-14 00:00:00 | 0 | 1.6795 | 1.6549
 |  |  |  |  | 2009-03-14 00:00:00 | 2009-03-15 00:00:00 | 0 | 1.6839 | 1.6667
 |  |  |  |  | 2009-03-15 00:00:00 | 2009-03-16 00:00:00 | 0 | 1.6885 | 1.6702
 |  |  |  |  | 2009-03-16 00:00:00 | 2009-03-17 00:00:00 | 0 | 1.6912 | 1.6654
 |  |  |  |  | 2009-03-17 00:00:00 | 2009-03-18 00:00:00 | 0 | 1.6902 | 1.6806
 |  |  |  |  | 2009-03-18 00:00:00 | 2009-03-19 00:00:00 | 0 | 1.6966 | 1.678
 |  |  |  |  | 2009-03-19 00:00:00 | 2009-03-20 00:00:00 | 0 | 1.701 | 1.6943
 |  |  |  |  | 2009-03-20 00:00:00 | 2009-03-21 00:00:00 | 0 | 1.6979 | 1.6789
 |  |  |  |  | 2009-03-21 00:00:00 | 2009-03-22 00:00:00 | 0 | 1.7059 | 1.6837
 |  |  |  |  | 2009-03-22 00:00:00 | 2009-03-23 00:00:00 | 0 | 1.7196 | 1.6927
 |  |  |  |  | 2009-03-23 00:00:00 | 2009-03-24 00:00:00 | 0 | 1.7187 | 1.7061
 |  |  |  |  | 2009-03-24 00:00:00 | 2009-03-25 00:00:00 | 0 | 1.7315 | 1.7089
 |  |  |  |  | 2009-03-25 00:00:00 | 2009-03-26 00:00:00 | -4.074986799501868 | 1.7282 | 1.6983
 |  |  |  |  | 2009-03-26 00:00:00 | 2009-03-27 00:00:00 | 70.1269760479042 | 1.714 | 1.6947
 |  |  |  |  | 2009-03-27 00:00:00 | 2009-03-28 00:00:00 | 0 | 1.7155 | 1.6798
 |  |  |  |  | 2009-03-28 00:00:00 | 2009-03-29 00:00:00 | 0 | 1.7005 | 1.6797
 |  |  |  |  | 2009-03-29 00:00:00 | 2009-03-30 00:00:00 | 0 | 1.6919 | 1.6626
 |  |  |  |  | 2009-03-30 00:00:00 | 2009-03-31 00:00:00 | 0 | 1.697 | 1.665
 |  |  |  |  | 2009-03-31 00:00:00 | 2009-04-01 00:00:00 | 0 | 1.6958 | 1.6716
 |  |  |  |  | 2009-04-01 00:00:00 | 2009-04-02 00:00:00 | 0 | 1.6829 | 1.6711
 |  |  |  |  | 2009-04-02 00:00:00 | 2009-04-03 00:00:00 | 0 | 1.692 | 1.676
 |  |  |  |  | 2009-04-03 00:00:00 | 2009-04-04 00:00:00 | 0 | 1.6968 | 1.6804
 |  |  |  |  | 2009-04-04 00:00:00 | 2009-04-05 00:00:00 | 0 | 1.699 | 1.6876
 |  |  |  |  | 2009-04-05 00:00:00 | 2009-04-06 00:00:00 | 0 | 1.7104 | 1.69
 |  |  |  |  | 2009-04-06 00:00:00 | 2009-04-07 00:00:00 | 0 | 1.7133 | 1.7
 |  |  |  |  | 2009-04-07 00:00:00 | 2009-04-08 00:00:00 | 0 | 1.7254 | 1.6917
 |  |  |  |  | 2009-04-08 00:00:00 | 2009-04-09 00:00:00 | 0 | 1.7254 | 1.6796
 |  |  |  |  | 2009-04-09 00:00:00 | 2009-04-10 00:00:00 | 0 | 1.6848 | 1.6653
 |  |  |  |  | 2009-04-10 00:00:00 | 2009-04-11 00:00:00 | 0 | 1.693 | 1.6711
 |  |  |  |  | 2009-04-11 00:00:00 | 2009-04-12 00:00:00 | 0 | 1.6884 | 1.6703
 |  |  |  |  | 2009-04-12 00:00:00 | 2009-04-13 00:00:00 | 0 | 1.6827 | 1.6724
 |  |  |  |  | 2009-04-13 00:00:00 | 2009-04-14 00:00:00 | 0 | 1.7044 | 1.6782
 |  |  |  |  | 2009-04-14 00:00:00 | 2009-04-15 00:00:00 | 0 | 1.717 | 1.6994
 |  |  |  |  | 2009-04-15 00:00:00 | 2009-04-16 00:00:00 | 0 | 1.7259 | 1.706
 |  |  |  |  | 2009-04-16 00:00:00 | 2009-04-17 00:00:00 | 0 | 1.7362 | 1.7101
 |  |  |  |  | 2009-04-17 00:00:00 | 2009-04-18 00:00:00 | 0 | 1.7386 | 1.7092
 |  |  |  |  | 2009-04-18 00:00:00 | 2009-04-19 00:00:00 | 0 | 1.7354 | 1.7202
 |  |  |  |  | 2009-04-19 00:00:00 | 2009-04-20 00:00:00 | 0 | 1.7274 | 1.7196
 |  |  |  |  | 2009-04-20 00:00:00 | 2009-04-21 00:00:00 | 0 | 1.7299 | 1.7221
 |  |  |  |  | 2009-04-21 00:00:00 | 2009-04-22 00:00:00 | 0 | 1.7478 | 1.7296
 |  |  |  |  | 2009-04-22 00:00:00 | 2009-04-23 00:00:00 | 2.811073846153846 | 1.745 | 1.7241
 |  |  |  |  | 2009-04-23 00:00:00 | 2009-04-24 00:00:00 | 0 | 1.7381 | 1.7156
 |  |  |  |  | 2009-04-24 00:00:00 | 2009-04-25 00:00:00 | 0 | 1.7609 | 1.7276
 |  |  |  |  | 2009-04-25 00:00:00 | 2009-04-26 00:00:00 | 0 | 1.7608 | 1.744
 |  |  |  |  | 2009-04-26 00:00:00 | 2009-04-27 00:00:00 | 0 | 1.7713 | 1.74
 |  |  |  |  | 2009-04-27 00:00:00 | 2009-04-28 00:00:00 | -3.519957035445757 | 1.7553 | 1.7225
 |  |  |  |  | 2009-04-28 00:00:00 | 2009-04-29 00:00:00 | 0 | 1.7404 | 1.7123
 |  |  |  |  | 2009-04-29 00:00:00 | 2009-04-30 00:00:00 | 0 | 1.7529 | 1.7262
 |  |  |  |  | 2009-04-30 00:00:00 | 2009-05-01 00:00:00 | -4.280831892230577 | 1.7636 | 1.7504
 |  |  |  |  | 2009-05-01 00:00:00 | 2009-05-02 00:00:00 | 0 | 1.7701 | 1.7569
 |  |  |  |  | 2009-05-02 00:00:00 | 2009-05-03 00:00:00 | 0 | 1.7797 | 1.765
 |  |  |  |  | 2009-05-03 00:00:00 | 2009-05-04 00:00:00 | 0 | 1.7818 | 1.7673
 |  |  |  |  | 2009-05-04 00:00:00 | 2009-05-05 00:00:00 | -14.23635774647887 | 1.7886 | 1.77
 |  |  |  |  | 2009-05-05 00:00:00 | 2009-05-06 00:00:00 | 0 | 1.7888 | 1.772
 |  |  |  |  | 2009-05-06 00:00:00 | 2009-05-07 00:00:00 | 0 | 1.7861 | 1.7658
 |  |  |  |  | 2009-05-07 00:00:00 | 2009-05-08 00:00:00 | 0 | 1.779 | 1.7471
 |  |  |  |  | 2009-05-08 00:00:00 | 2009-05-09 00:00:00 | 0 | 1.7868 | 1.7713
 |  |  |  |  | 2009-05-09 00:00:00 | 2009-05-10 00:00:00 | 0 | 1.793 | 1.7737
 |  |  |  |  | 2009-05-10 00:00:00 | 2009-05-11 00:00:00 | 0 | 1.7759 | 1.7474
 |  |  |  |  | 2009-05-11 00:00:00 | 2009-05-12 00:00:00 | 9.855 | 1.8115 | 1.753
 |  |  |  |  | 2009-05-12 00:00:00 | 2009-05-13 00:00:00 | 0 | 1.8052 | 1.7818
 |  |  |  |  | 2009-05-13 00:00:00 | 2009-05-14 00:00:00 | -0.0841879120879121 | 1.8003 | 1.7853
 |  |  |  |  | 2009-05-14 00:00:00 | 2009-05-15 00:00:00 | 56.18851208360549 | 1.8011 | 1.7882
 |  |  |  |  | 2009-05-15 00:00:00 | 2009-05-16 00:00:00 | 0 | 1.8071 | 1.7829
 |  |  |  |  | 2009-05-16 00:00:00 | 2009-05-17 00:00:00 | 0 | 1.7889 | 1.7688
 |  |  |  |  | 2009-05-17 00:00:00 | 2009-05-18 00:00:00 | 0 | 1.7825 | 1.7617
 |  |  |  |  | 2009-05-18 00:00:00 | 2009-05-19 00:00:00 | 0 | 1.7811 | 1.7717
 |  |  |  |  | 2009-05-19 00:00:00 | 2009-05-20 00:00:00 | 0 | 1.7735 | 1.7569
 |  |  |  |  | 2009-05-20 00:00:00 | 2009-05-21 00:00:00 | 0 | 1.7671 | 1.7524
 |  |  |  |  | 2009-05-21 00:00:00 | 2009-05-22 00:00:00 | 0 | 1.7599 | 1.7469
 |  |  |  |  | 2009-05-22 00:00:00 | 2009-05-23 00:00:00 | 0 | 1.7636 | 1.7493
 |  |  |  |  | 2009-05-23 00:00:00 | 2009-05-24 00:00:00 | 0 | 1.7749 | 1.7534
 |  |  |  |  | 2009-05-24 00:00:00 | 2009-05-25 00:00:00 | 0 | 1.7586 | 1.7392
 |  |  |  |  | 2009-05-25 00:00:00 | 2009-05-26 00:00:00 | 0 | 1.7781 | 1.7588
 |  |  |  |  | 2009-05-26 00:00:00 | 2009-05-27 00:00:00 | 0 | 1.7785 | 1.762
 |  |  |  |  | 2009-05-27 00:00:00 | 2009-05-28 00:00:00 | 0 | 1.7698 | 1.7596
 |  |  |  |  | 2009-05-28 00:00:00 | 2009-05-29 00:00:00 | 0 | 1.7644 | 1.7547
 |  |  |  |  | 2009-05-29 00:00:00 | 2009-05-30 00:00:00 | 0 | 1.7705 | 1.757
 |  |  |  |  | 2009-05-30 00:00:00 | 2009-05-31 00:00:00 | 0 | 1.767 | 1.7491
 |  |  |  |  | 2009-05-31 00:00:00 | 2009-06-01 00:00:00 | 0 | 1.7555 | 1.7449
 |  |  |  |  | 2009-06-01 00:00:00 | 2009-06-02 00:00:00 | 0 | 1.7721 | 1.753
 |  |  |  |  | 2009-06-02 00:00:00 | 2009-06-03 00:00:00 | 0 | 1.7745 | 1.7543
 |  |  |  |  | 2009-06-03 00:00:00 | 2009-06-04 00:00:00 | 0 | 1.7669 | 1.755
 |  |  |  |  | 2009-06-04 00:00:00 | 2009-06-05 00:00:00 | -32.59873562106211 | 1.7733 | 1.7608
 |  |  |  |  | 2009-06-05 00:00:00 | 2009-06-06 00:00:00 | 0 | 1.7865 | 1.7595
 |  |  |  |  | 2009-06-06 00:00:00 | 2009-06-07 00:00:00 | 0 | 1.8027 | 1.7787
 |  |  |  |  | 2009-06-07 00:00:00 | 2009-06-08 00:00:00 | 0 | 1.8021 | 1.7803
 |  |  |  |  | 2009-06-08 00:00:00 | 2009-06-09 00:00:00 | 0 | 1.8031 | 1.7876
 |  |  |  |  | 2009-06-09 00:00:00 | 2009-06-10 00:00:00 | 0 | 1.8036 | 1.7915
 |  |  |  |  | 2009-06-10 00:00:00 | 2009-06-11 00:00:00 | 0 | 1.8091 | 1.7934
 |  |  |  |  | 2009-06-11 00:00:00 | 2009-06-12 00:00:00 | 0 | 1.809 | 1.7869
 |  |  |  |  | 2009-06-12 00:00:00 | 2009-06-13 00:00:00 | 0 | 1.8076 | 1.7791
 |  |  |  |  | 2009-06-13 00:00:00 | 2009-06-14 00:00:00 | 0 | 1.8073 | 1.7853
 |  |  |  |  | 2009-06-14 00:00:00 | 2009-06-15 00:00:00 | 0 | 1.7914 | 1.7805
 |  |  |  |  | 2009-06-15 00:00:00 | 2009-06-16 00:00:00 | 0 | 1.785 | 1.7728
 |  |  |  |  | 2009-06-16 00:00:00 | 2009-06-17 00:00:00 | 0 | 1.7868 | 1.7716
 |  |  |  |  | 2009-06-17 00:00:00 | 2009-06-18 00:00:00 | -4.432267600262596 | 1.7779 | 1.7677
 |  |  |  |  | 2009-06-18 00:00:00 | 2009-06-19 00:00:00 | -28.34514077253218 | 1.7733 | 1.7591
 |  |  |  |  | 2009-06-19 00:00:00 | 2009-06-20 00:00:00 | 2.091222689463956 | 1.7836 | 1.7585
 |  |  |  |  | 2009-06-20 00:00:00 | 2009-06-21 00:00:00 | 0 | 1.7828 | 1.7557
 |  |  |  |  | 2009-06-21 00:00:00 | 2009-06-22 00:00:00 | 0 | 1.7694 | 1.7535
 |  |  |  |  | 2009-06-22 00:00:00 | 2009-06-23 00:00:00 | 19.62805575 | 1.7569 | 1.7416
 |  |  |  |  | 2009-06-23 00:00:00 | 2009-06-24 00:00:00 | 29.83960521042084 | 1.7531 | 1.7391
 |  |  |  |  | 2009-06-24 00:00:00 | 2009-06-25 00:00:00 | 0 | 1.743 | 1.7314
 |  |  |  |  | 2009-06-25 00:00:00 | 2009-06-26 00:00:00 | 13.68813903345725 | 1.7375 | 1.7257
 |  |  |  |  | 2009-06-26 00:00:00 | 2009-06-27 00:00:00 | 0 | 1.7355 | 1.7156
 |  |  |  |  | 2009-06-27 00:00:00 | 2009-06-28 00:00:00 | 0 | 1.7302 | 1.7195
 |  |  |  |  | 2009-06-28 00:00:00 | 2009-06-29 00:00:00 | 0 | 1.7261 | 1.7184
 |  |  |  |  | 2009-06-29 00:00:00 | 2009-06-30 00:00:00 | 0 | 1.7297 | 1.7159
 |  |  |  |  | 2009-06-30 00:00:00 | 2009-07-01 00:00:00 | 0 | 1.7334 | 1.7191