Array formula

[Pefird]

A	B	C	D	E	F	G	H	I	J	K	L	M
Date	Route	Product 1	Product 2	Product 3					MAY
May 1	3	23	32	29					Route	P1	P2	P3
May 2	5	35	53	71					3
May 3	6	14	27	12					4
May 4	5	18	81	65					5
April 30	3	54	19	21					6

<tbody>
</tbody>

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