Sumifs Across a Table Array

[Confidentjohn]

Hi

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

my data looks like

	Sent
Who	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Nov-14	Dec-14	SignUp Date
a	1	2	3	4	5	6	7	8	9	Aug-13
b	2	3	4	765	766	767	768	769	770	Sep-13
c	3	4	5	6	7	8	9	10	11	Oct-13
d	4	543	544	545	546	547	548	549	550	Nov-13
e	5	6	7	8	9	10	11	12	13	Dec-13
f	6	7	654	655	656	657	658	659	660	Oct-13
g	7	8	9	10	11	12	13	14	15	Oct-13
h	8	9	54	55	56	57	58	59	60	Mar-14
i	9	10	11	12	13	5	6	7	8	Oct-13
j	10	11	12	13	14	15	16	17	18	Aug-13
fd	11	34	35	36	37	38	39	40	41	Aug-13
fd	12	13	14	15	16	17	18	19	20	Aug-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

 

[Special-K99]

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]

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

 

[Oeldere]

how about an pivot table solution?

 

[Confidentjohn]

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]

(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.

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