# Sumifs Across a Table Array

#### Confidentjohn

##### Board Regular
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

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

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

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

how about an pivot table solution?

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.

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.

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
End If

With Sheets("Output")
.UsedRange.ClearContents

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)
cl.Rows.EntireRow.Delete
End If
Next

Columns("A:Z").EntireColumn.AutoFit

End With
End Sub``````

Replies
5
Views
183
Replies
5
Views
533
Replies
5
Views
117
Replies
6
Views
201
Replies
1
Views
238

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.

### Which adblocker are you using?

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

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