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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,629
Messages
5,838,454
Members
430,549
Latest member
jayjay2022

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
Top