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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
(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
 
Upvote 0

Forum statistics

Threads
1,218,746
Messages
6,144,265
Members
450,533
Latest member
xoxo1998

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