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.

 

  JanFebMarAprilMayJunJulAug
TelephoneUtilities135621872107016771125548770
Federal TaxTaxes593851662195086882723141687
State TaxTaxes508124517332110799414521410
ElectricityUtilities9501469386704122519674581226
Office SuppliesSupplies61010326315961405135052209
GarbageUtilities1073117513527341025438998806
BagsSupplies18826224302611544291552347
UniformsSupplies435462901455174417765544897
Personal TaxesTaxes19618441054619106413375951767

 

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

           

4 Replies

@Humphriesre 

 

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".

@Humphriesre 

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

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:

=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. 

 

@Humphriesre 

=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.

sum january to end month.JPG