Sum values by date range in multiple columns

trimiii

Board Regular
Joined
May 15, 2018
Messages
69
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Can you explain in words what you are trying to count?
 
Upvote 0
Hi Peter,

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

Attachments

  • Tim.PNG
    Tim.PNG
    12.7 KB · Views: 41
Upvote 0
If your From/To dates are always complete months like your example, then try this copied across and down.

Book1
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
13FORD33
14BMW33
SUMPRODUCT
Cell Formulas
RangeFormula
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))
 
Upvote 0
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?
 
Upvote 0
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

Book1
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
13FORD33
14BMW33
SUMPRODUCT (2)
Cell Formulas
RangeFormula
P13:P14, R13:R14P13=CountBrandDate($P$2:$Z$8,$B$2:$B$8,P$11,P$12,$B13)
 
Upvote 0
You're welcome and thanks for your kind words. :)
 
Upvote 0
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

  • Tim1.PNG
    Tim1.PNG
    119.5 KB · Views: 14
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,760
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top