Long-time lurker, first time poster. I've read FAQs, and searched, but suspect I'm using the wrong search terms.
I've got a table with two dimensional data. Dates, monthly, along the top by column. Projects, including start date, along the left, by row. I'm trying to SUMIFS the data, such that I only want positive values and prior to a date in a reference cell. A link to OneDrive example sheet is below but I'll also try showing an example here:
<tbody>
</tbody>
The (56) in the 7/31/19:7/31/19 cell is the sum of all positive figures above and prior to 7/31/19
The formulas I've tried for this look like this: SUMIFS($b$2:$z4,$b$2:$z4,">0",$b$2:$z$2,"<"&$a5) or SUMPRODUCT(--($b$2:$z$2<$B15),--($b$2:$z4>0),$b$2:$z4)
I'm indifferent between a SUMIFS solution or SUMPRODUCT solution, but the only way I've been able to avoid a #VALUE error is by removing the date condition and manually adjusting the columns I'm summing.
Thanks in advance and apologies if I broke protocol or did a poor job of explaining.
https://1drv.ms/x/s!Ajx8pq4MoHlScLyCjceXZYB3VlQ
I've got a table with two dimensional data. Dates, monthly, along the top by column. Projects, including start date, along the left, by row. I'm trying to SUMIFS the data, such that I only want positive values and prior to a date in a reference cell. A link to OneDrive example sheet is below but I'll also try showing an example here:
4/30/19 | 5/31/19 | 6/30/19 | 7/31/19 | ||||
4/30/19 | (425) | 6 | 6 | 6 | |||
5/31/19 | (1244) | 11 | 11 | ||||
6/30/19 | (2182) | 34 | |||||
7/31/19 | (56) |
<tbody>
</tbody>
The (56) in the 7/31/19:7/31/19 cell is the sum of all positive figures above and prior to 7/31/19
The formulas I've tried for this look like this: SUMIFS($b$2:$z4,$b$2:$z4,">0",$b$2:$z$2,"<"&$a5) or SUMPRODUCT(--($b$2:$z$2<$B15),--($b$2:$z4>0),$b$2:$z4)
I'm indifferent between a SUMIFS solution or SUMPRODUCT solution, but the only way I've been able to avoid a #VALUE error is by removing the date condition and manually adjusting the columns I'm summing.
Thanks in advance and apologies if I broke protocol or did a poor job of explaining.
https://1drv.ms/x/s!Ajx8pq4MoHlScLyCjceXZYB3VlQ