Menu
I've got a spreadsheet like this:
date | 7/1 | 7/2 | 7/3 | 7/4
-----|-----|-----|-----|-----
val | 3 | 5 | 1 | 3
-----|-----|-----|-----|-----
I want to sum the val row, but only up to the current date. So if today were 7/3, the sum would be 3+5+1=9. If today were 7/4, it would be 12.
I figured this out to get the number of columns:
=YEARFRAC(B1,TODAY())*360 // B1 is the first date -- 7/1
but I can't figure out how to tell excel to do the sum:
=SUM(B2:<B+num cols above>2)
Presumably its something to do with references, and lookup, but I'm not really familiar with how those work....
467
COUNTIFS - Count in Date Range If you need to count the number of cells, based on one or more criteria, use the COUNTIFS function. In the example below, the formula will count the rows where the date (column A) is within our set date range.
For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John." To sum cells based on multiple criteria, see SUMIFS function.
Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you're done. When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.
You can use SUMIF:
=SUMIF(A1:E1,"<="&TODAY(),A2:E2)
Assuming your dates are in a1:e1 and your values are in a2:e2.
181
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
