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.       Jan Feb ...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Aug 15, 2022

Humphriesre 

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

You can try this formula for the data layout of the 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. 

 

Resources