Hello!
Could someone help me with changing the SUMIFS formula to IndexMatch that would be able to sum a value within a certain time frame?
I started making the SUMIFS, but found out that once the separate file closes the formula goes haywire. I'm trying to remake the formula to Index Match formula since it would be able to pull the data without needing the data file open.
But I'm having a problem with the formula to pull the date range.
Please see below for reference.
Thanks in advance!
mrmerp
<tbody>
</tbody>
Could someone help me with changing the SUMIFS formula to IndexMatch that would be able to sum a value within a certain time frame?
I started making the SUMIFS, but found out that once the separate file closes the formula goes haywire. I'm trying to remake the formula to Index Match formula since it would be able to pull the data without needing the data file open.
But I'm having a problem with the formula to pull the date range.
Please see below for reference.
Thanks in advance!
mrmerp
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |
1 | Date | 4/30/2019 | SUMIFS(E6:P6,E5:P5,"<="&EOMONTH(B1,0),E5:P5,">="&EOMONTH(B1,-3)+1) | |||||||||||||
2 | 2/28/2019 | Total | 306 | |||||||||||||
3 | ||||||||||||||||
4 | Date | 1/31/2019 | 2/28/2019 | 3/31/2019 | 4/30/2019 | 5/31/2019 | 6/30/2019 | 7/31/2019 | 8/31/2019 | 9/30/2019 | 10/31/2019 | 11/30/2019 | 12/31/2019 | |||
5 | Revenue | 100 | 101 | 102 | 103 | 104 | 105 | 106 | 107 | 108 | 109 | 110 | 111 | |||
INDEX(D5:P6,MATCH(D6,D5:D6,0),MATCH(B3&B2,(D5:P5>=C3)*(D5:P5<=C2),0)) | ||||||||||||||||
Total | #N/A |
<tbody>
</tbody>