Array formula

[Pefird]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]Route
[/TD]
[TD]Product 1
[/TD]
[TD]Product 2
[/TD]
[TD]Product 3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MAY
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May 1
[/TD]
[TD]3
[/TD]
[TD]23
[/TD]
[TD]32
[/TD]
[TD]29
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Route
[/TD]
[TD]P1
[/TD]
[TD]P2
[/TD]
[TD]P3
[/TD]
[/TR]
[TR]
[TD]May 2
[/TD]
[TD]5
[/TD]
[TD]35
[/TD]
[TD]53
[/TD]
[TD]71
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May 3
[/TD]
[TD]6
[/TD]
[TD]14
[/TD]
[TD]27
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]May 4
[/TD]
[TD]5
[/TD]
[TD]18
[/TD]
[TD]81
[/TD]
[TD]65
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]April 30
[/TD]
[TD]3
[/TD]
[TD]54
[/TD]
[TD]19
[/TD]
[TD]21
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Cant get this figured out, hope its even possible.

I want to total routes by the month. So see if I can explain this well enough.
I have a daily worksheet I fill out with date, route and products used on individual routes.

In another worksheet, I would like to keep a record of the amount of product used per month for each route.
I have been trying to use =Sumifs to compare the Month() in Column A to the Month() in J1 and while also comparing the Column B to the individual cell routes in Column J. Summing C,D,E into K,L,M respectively.

This doesn't match the table above, but...
{=sumifs('[Activity.xlsx]Night'!$J:$J,month('[Activity.xlsx]Night'!$B:$b),"="&month($B$1),'[Activity.xlsx]Night'!$c:$C,"="&$B4)}

This is my attempt...but Excel says that it isn't even a formula. Obviously doing something very wrong.

Thanks for your help!

 

[Fluff]

Re: Help with an array formula

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Route	Product 1	Product 2	Product 3					01/05/2019
2	May-01	3	23	32	29					Route	P1	P2	P3
3	May-02	5	35	53	71					3	23	32	29
4	May-03	6	14	27	12					4	0	0	0
5	May-04	5	18	81	65					5	53	134	136
6	Apr-30	3	54	19	21					6	14	27	12
Names

Cell Formulas
Range	Formula
K3	=SUMPRODUCT((MONTH($A$2:$A$6)=MONTH($J$1))*($B$2:$B$6=$J3),C$2:C$6)

 

[kweaver]

Re: Help with an array formula

I put a helper column F that was: =MONTH(A2) and fill down.
In J1, I put 5/1 so that the actual entry is a serial number, then a helper in K1: =month(J1)



Then, K3 looks like the image and filled down and across.

You could move the J - M to another sheet and change the formula accordingly using the sheet names.

 

[kweaver]

Re: Help with an array formula

Fluff: good alternative.

 

[Pefird]

Re: Help with an array formula

Thanks to all the options. The sumproduct worked well. I was overthinking it. Thank you for reminding me of the sumproduct, I've used it before but didn't think of it at all.

 

[Fluff]

Re: Help with an array formula

Glad we could help & thanks for the feedback