New Contributor

# Sum Date range by criteria

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.

 Jan Feb Mar April May Jun Jul Aug Telephone Utilities 135 621 872 1070 1677 1125 548 770 Federal Tax Taxes 59 385 1662 1950 868 827 2314 1687 State Tax Taxes 508 1245 173 32 1107 994 1452 1410 Electricity Utilities 950 1469 386 704 1225 1967 458 1226 Office Supplies Supplies 610 1032 63 1596 1405 1350 52 209 Garbage Utilities 1073 1175 1352 734 1025 438 998 806 Bags Supplies 1882 622 430 26 1154 429 1552 347 Uniforms Supplies 435 462 901 455 1744 1776 5544 897 Personal Taxes Taxes 1961 844 1054 619 1064 1337 595 1767

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

4 Replies

# Re: Sum Date range by criteria

Adjust the ranges in the formula

=SUMPRODUCT(\$C\$2:\$H\$10*(\$B\$2:\$B\$10="Utilities"))

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

# Re: Sum Date range by criteria

``=SUMPRODUCT((\$C\$2:\$H\$10)*(\$B\$2:\$B\$10=A13))``

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

# Re: Sum Date range by criteria

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:

``````=SUMPRODUCT(C2:H10,
(B2:B10="Utilities")*
(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.

# Re: Sum Date range by criteria

``=SUMPRODUCT((\$B\$2:\$B\$10=A13)*OFFSET(\$C\$2:\$C\$10,0,0,9,MATCH(\$B\$12,\$C\$1:\$J\$1,0)))``

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