Sumifs Across a Table Array

Confidentjohn

Board Regular
Joined
Mar 3, 2009
Messages
73
Hi

I am trying to see if it is possible to do a sumifs across an array of data

my data looks like
Sent
WhoApr-14May-14Jun-14Jul-14Aug-14Sep-14Oct-14Nov-14Dec-14SignUp Date
a123456789Aug-13
b234765766767768769770Sep-13
c34567891011Oct-13
d4543544545546547548549550Nov-13
e5678910111213Dec-13
f67654655656657658659660Oct-13
g789101112131415Oct-13
h8954555657585960Mar-14
i9101112135678Oct-13
j101112131415161718Aug-13
fd113435363738394041Aug-13
fd121314151617181920Aug-13

<colgroup><col><col span="9"><col></colgroup><tbody>
</tbody>

I want to find the total number of sends in Jun 14 for people who signed up in Oct 13

The answer should be 679,
Ive tried a sumproduct and a sumifs across the range but can't get it to work

Could anyone help me out please

Cheers

John
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Try
=SUMPRODUCT((K2:K14=DATEVALUE("2014-10-13"))*(D2:D14))

where column D is Jun-14
column K is Sign up date

You may have to adjust the date in DATEVALUE to the correct fromat
 

Confidentjohn

Board Regular
Joined
Mar 3, 2009
Messages
73
Hi Thanks for this,

However is there a way I could do this and specify the whole data set range, I am going to need to repeat this task for each month on a much larger data set, would be great to have a formula that I can drag across and match change to changeable criteria
 

Confidentjohn

Board Regular
Joined
Mar 3, 2009
Messages
73
A pivot table would work, but would prefer to do this with formulas, due to ever changing data sets, which Would be easier to calculate in formulas,

If not possible no worries, but always worth a go.
 

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,213
(oeldere) 2014-09-11 mr excel.xlsm downloaden

I posted the file on free dutch site.

I used the code below to re-arange the data.

After that i used a pivot table to get the result.

Please reply.

Code:
Sub CONVERTROWSTOCOL_Oeldere_revisted()

Dim rsht1 As Long, rsht2 As Long, I As Long, col As Long, wsTest As Worksheet

'check if sheet "ouput" already exist

Const strSheetName As String = "Output"
 
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

   
   With Sheets("Output")
    .UsedRange.ClearContents
    .Range("A1:D1").Value = Array("Who", "Sign up data", "Month", "value")

    End With
    
    
    rsht1 = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
    rsht2 = Sheets("Output").Range("A" & Rows.Count).End(xlUp).Row
    col = 2
    
    For I = 2 To rsht1
            Do While Sheets("sheet1").Cells(1, col).Value <> "" 'And Sheets("sheet1").Cells(i, col).Value <> ""
            rsht2 = rsht2 + 1
           
            Sheets("Output").Range("A" & rsht2).Value = Sheets("sheet1").Range("A" & I).Value
            
            Sheets("Output").Range("B" & rsht2).Value = Sheets("sheet1").Range("K" & I).Value
            
              
            Sheets("Output").Range("C" & rsht2).Value = Sheets("sheet1").Cells(1, col).Value
            
            Sheets("Output").Range("D" & rsht2).Value = Sheets("sheet1").Cells(I, col).Value
            
     
            col = col + 1
        Loop
        col = 2
    Next
  With Sheets("Output")
  
  For Each cl In .Range("C2:C" & .Rows.Count)
    If cl = "Sign up data" Then
    cl.Rows.EntireRow.Delete
    End If
    Next
       
   Columns("A:Z").EntireColumn.AutoFit
    
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,181
Members
409,856
Latest member
MAO
Top