# Sum values by date range in multiple columns

#### trimiii

##### New Member
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
Can you explain in words what you are trying to count?

#### trimiii

##### New Member
Hi Peter,

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

#### Attachments

• 12.7 KB Views: 11

#### Peter_SSs

##### MrExcel MVP, Moderator
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
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
is there any way to simplify this formula to use for entire sheet?
Not coming to mind, sorry.

#### Peter_SSs

##### MrExcel MVP, Moderator
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)

#### trimiii

##### New Member
Hi Peter,
you are a STAR my friend.

thanks again

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome and thanks for your kind words.

#### trimiii

##### New Member
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:

#### Attachments

• 119.5 KB Views: 6