Sum values by date range in multiple columns

trimiii

New Member
Joined
May 15, 2018
Messages
41
Hi,

I need help, I am using this formula =SUMPRODUCT(--(R6:R29>=D39), --(R6:R29<=D40), --(B6:B29=B41)) which counts my dates only in one column, but I need to expand to provide the result for more than 3 columns.
As you can see this formula is only for column R, how do I include mroeo column such R,S,T... Z?

thanks.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,725
Office Version
365
Platform
Windows
Can you explain in words what you are trying to count?
 

trimiii

New Member
Joined
May 15, 2018
Messages
41
Hi Peter,

I am trying to count the value between dates in multi-columns, see picture below...
 

Attachments

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,725
Office Version
365
Platform
Windows
If your From/To dates are always complete months like your example, then try this copied across and down.

xl2bb.xlam
BPR
2FORD1/08/20191/08/2019
3FORD1/08/20191/09/2019
4FORD5/09/2019
5BMW10/08/201915/09/2019
6BMW1/09/20196/08/2019
7BMW5/08/201916/09/2019
8FORD1/08/201930/09/2019
9
10
11From1/08/20191/09/2019
12To31/08/201930/09/2019
13FORD
14BMW
SUMPRODUCT
Cell Formulas
Range(s)Formula
P13:P14, R13:R14P13=SUMPRODUCT(--ISNUMBER(FIND(TEXT(P$11,"mmyy"),TEXT($P$2:$P$8,"mmyy")&"|"&TEXT($R$2:$R$8,"mmyy"))),--($B$2:$B$8=$B13))
 

trimiii

New Member
Joined
May 15, 2018
Messages
41
Hi Peter,

this works just fine, thanks a lot it makes my life easy but just one question... is there any way to simplify this formula to use for entire sheet?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,725
Office Version
365
Platform
Windows
If a vba user-defined function is acceptable ...
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across & down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

VBA Code:
Function CountBrandDate(rDates As Range, rBrands As Range, dStart As Date, dEnd As Date, sBrand As String) As Long
  Dim vDates As Variant, vBrands As Variant
  Dim r As Long, c As Long, ubDates2 As Long
 
  vDates = rDates.Value
  ubDates2 = UBound(vDates, 2)
  vBrands = rBrands.Value
  For r = 1 To UBound(vBrands)
    If vBrands(r, 1) = sBrand Then
      For c = 1 To ubDates2
        If IsDate(vDates(r, c)) Then
          If vDates(r, c) >= dStart And vDates(r, c) <= dEnd Then
            CountBrandDate = CountBrandDate + 1
            Exit For
          End If
        End If
      Next c
    End If
  Next r
End Function
xl2bb.xlam
BPR
2FORD1/08/20191/08/2019
3FORD1/08/20191/09/2019
4FORD5/09/2019
5BMW10/08/201915/09/2019
6BMW1/09/20196/08/2019
7BMW5/08/201916/09/2019
8FORD1/08/201930/09/2019
9
10
11From1/08/20191/09/2019
12To31/08/201930/09/2019
13FORD
14BMW
SUMPRODUCT (2)
Cell Formulas
Range(s)Formula
P13:P14, R13:R14P13=CountBrandDate($P$2:$Z$8,$B$2:$B$8,P$11,P$12,$B13)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,725
Office Version
365
Platform
Windows
You're welcome and thanks for your kind words. :)
 

trimiii

New Member
Joined
May 15, 2018
Messages
41
Hi Peter, its me again...

not sure if I did some wrong but this is my result... can you please advise? it only counts me for the total sum of 2

see picture below:

Help, please...
 

Attachments

Forum statistics

Threads
1,077,825
Messages
5,336,596
Members
399,091
Latest member
Mcoca

Some videos you may like

This Week's Hot Topics

Top