Sum Date range by criteria

New Contributor

I need to find the value of a date range by criteria that may repeat in the row. This is just a simple idea of what I might need. I at times have 4 to 10 different categories.


Federal TaxTaxes593851662195086882723141687
State TaxTaxes508124517332110799414521410
Office SuppliesSupplies61010326315961405135052209
Personal TaxesTaxes19618441054619106413375951767


I need to be able to find total between dates, such as Jan to Jun or Jan to Dec.


4 Replies



Adjust the ranges in the formula




and similar for the other categories. You can, of course, refer to a cell containing Utilities instead of specifying the literal value "Utilities".



You can try this formula for the data layout of the example.

total of categories.JPG 

so to add the date range adds some complexity.  It would be easier if those months (e.g. "Jan") are actually excel dates (e.g. "Jan 1, 2022") but formatted to only display the month.  For this exercise I will assume that is NOT the case and used the following:

           (DATEVALUE(C1:H1&" 1, 2020")>=DATEVALUE("Jan 1, 2020"))*
           (DATEVALUE(C1:H1&" 1, 2020")<DATEVALUE("Jul 1, 2020")))

you could easily replace the "Jan 1,2020" with a cell reference if as you like but this at least gives you an idea of the technique you can use.  see attached. 




For the sum from january to any end month you can try this formula. Enter the end month in cell B12 in this example.

sum january to end month.JPG