Sumifs across rows and columns

[CarolynS]

I have a data set with unique id numbers down the left hand colum and dates (week commencing) across the top. For each unique ID I want to sum the data if it falls under a specific month. So for example I want to say for the unique id number, if the date contains the month of october then sum this data. I would normally use a sumifs for this but I'm not sure how to do this when the criteria is a column header? Not sure if it needs to be combined with Index match but not sure how?

Thanks

Example table below

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]10 Oct 16[/TD]
[TD]17 Oct 16[/TD]
[TD]24 Oct 16[/TD]
[TD]31 Oct 16[/TD]
[TD]7 Nov 16[/TD]
[/TR]
[TR]
[TD]Unique ID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]070[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]071[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]072[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]073[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[AliGW]

I believe this does what you want:

Excel 2016 (Windows) 32 bit
	K	L	M	N	O	P	Q
3			10-Oct-16	17-Oct-16	24-Oct-16	31-Oct-16	07-Nov-16
4		Unique ID
5		70	5	4	2	6	9
6		71	3	6	7	2	6
7		72	6	8	1	4	1
8		73	9	2	8	8	3
9
10	ID	70	17
11	Month	10
Sheet4

Cell Formulas
Range	Formula
M10	=SUMPRODUCT((MONTH(M3:Q3)=L11)*(L5:L8=L10),M5:Q8)

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

10-Oct-16​

[/TD]
[TD]

17-Oct-16​

[/TD]
[TD]

24-Oct-16​

[/TD]
[TD]

31-Oct-16​

[/TD]
[TD]

7-Nov-16​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Unique ID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

70​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

2​

[/TD]
[TD]

6​

[/TD]
[TD]

9​

[/TD]
[TD]

17​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

71​

[/TD]
[TD]

3​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

2​

[/TD]
[TD]

6​

[/TD]
[TD]

18​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

72​

[/TD]
[TD]

6​

[/TD]
[TD]

8​

[/TD]
[TD]

1​

[/TD]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD]

19​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

73​

[/TD]
[TD]

9​

[/TD]
[TD]

2​

[/TD]
[TD]

8​

[/TD]
[TD]

8​

[/TD]
[TD]

3​

[/TD]
[TD]

27​

[/TD]
[/TR]
</tbody>[/TABLE]

G3=SUMPRODUCT(($A$3:$A$6=A3)*(MONTH($B$1:$F$1)=10)*($B$3:$F$6)) copy down

 

[AliGW]

G3=SUMPRODUCT(($A$3:$A$6=A3)*(MONTH($B$1:$F$1)=10)*($B$3:$F$6))

I think the section in red should be $B3:$F6, otherwise it will not increment as you drag down.

 

[CarolynS]

Thank you both. I shall give this a go

 

[CarolynS]

I have amended the formula slightly to fit my data set and it is working I am currently using:

=SUMPRODUCT((MONTH('L&D Consultant'!E2:BA2)=MONTH(F1))*('L&D Consultant'!D10:D16=D2),'L&D Consultant'!E10:BA16)

L&D Consultant is the name of the tab my data is sat in. The formula is sat in a dfferent tab (called 'Calcs') and is looking at cell F1 to obtain the month (i've added in month() here as this cell contains a full date) and cell D2 as this contains the unique ID i want to look up.

My data is split out across mutliple sheets depending on the role type of employees. So for example, there might be one tab called 'L&D Consultant' and one called 'Manual Labourer' etc. Ideally I'd like the formula to automatically pick up the role type (the role type is specified in the calcs tab next to the unique id) and use this to determine the name of the sheet it looks in. Is there a way to do this other than using a nested if?

Also the range area for each role type is different, i.e.the data range on the L&D Consultant sheet is E10:BA16 but in the Manual Labourer sheet it might be E10:BA100. Is there a way to get the formula to take this in to account? I'm looking to remove as much manual intervention as possible. The workbook will be updated regularly and the range sizes will change. Each sheet may also have more than one set of data within it for each role within the role type i.e. L&D Consultant may contain 2 or 3 roles, each role has a set of data within the L&D Consultant sheet but each set if data is seperated by several blank rows. So, depending on the role, the range will need to be different in the formula

Does that make any sense?

 

[CarolynS]

I have now used indirect to use the role type in column B to determine the sheet name where required and have set the range to go down to row 200 as none of my data sets will exceed 200 rows. All seems to be working - just need to test it

=SUMPRODUCT((MONTH(INDIRECT("'"&B2&"'!E2:BA2"))=MONTH(F1))*(INDIRECT("'"&B2&"'!D10:D200")=D2),INDIRECT("'"&B2&"'!E10:BA200"))