Forum Discussion

Humphriesre's avatar
Humphriesre
Copper Contributor
Aug 15, 2022

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.

 

  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.

           

excel
Formulas and Functions

4 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Aug 15, 2022

    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.

     

    • mtarler's avatar
      mtarler
      Silver Contributor
      Aug 15, 2022

      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. 

       

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 15, 2022

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

Resources