Cell Addition by month

[davidmor]

Hi

i have the below sheet im working on.

on the summary tracker by month tab i need it to add the information from the FG warehouse tracker

in cell B10 on the summary tab i have all inputs for October (total pallets checked that month) this is from column L in the FG warehouse tab

in cell B11 i need the total input for columns M & N within the same period, i can get it to put one of these in but cant grasp how to get it to provide the totals for each month in the cell.

i want to try and avoid having an extra table just for the totals if possible

Perpetual Tracker FG - RM 2022-2023 WIP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23
2
3
4	Warehouse 1
5	Pallets Checked
6	Pallets Incorrect
7	Accuracy
8
9	warehouse 2
10	Pallets Checked	2742
11	Pallets Incorrect	2
12	Accuracy
13
14
15
16
17
18	Over 98% is green
19	Below 98% is Red
20
Summary Tracker by Month

Cell Formulas
Range	Formula
B10	B10	=SUMIF('FG Warehouse Perpetual Tracker'!K4:K39,"<=31/10/2022",'FG Warehouse Perpetual Tracker'!L4:L38)
B11	B11	=SUMIF('FG Warehouse Perpetual Tracker'!K4:K39,"<=31/10/2022",'FG Warehouse Perpetual Tracker'!M4:M39)

Perpetual Tracker FG - RM 2022-2023 WIP.xlsx
	K	L	M	N	O
3	Date	Pallets Counted	Not issued	Not In stock	Wrong location
4	04/10/22	2604	2	1	1
5	19/10/22	128		1	1
6	31/10/22	10			1
7	01/11/22
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
FG Warehouse Perpetual Tracker

Thanks For any input

 

[Fluff]

How about in B10 copied across

=SUMIFS('FG Warehouse Perpetual Tracker'!$L$4:$L$39,'FG Warehouse Perpetual Tracker'!$K$4:$K$39,">="&B$1,'FG Warehouse Perpetual Tracker'!$K$4:$K$39,"<="&EOMONTH(B$1,0))

 

[davidmor]

=SUMIFS('FG Warehouse Perpetual Tracker'!$L$4:$L$39,'FG Warehouse Perpetual Tracker'!$K$4:$K$39,">="&B$1,'FG Warehouse Perpetual Tracker'!$K$4:$K$39,"<="&EOMONTH(B$1,0))

Hi Fluff

just tried that and it returns the same value 2742 as B10,

i need it to return the totals for M & N so from this example 4. currently its showing 2 as i can only get it to bring one value in rather than add them all together

 

[Fluff]

I'm not sure what you are saying, if 2742 is wrong, what is the correct answer & why?

 

[davidmor]

Sorry not sure if explained it well

Cell B10 is the total pallets counted

Cell B11 should be the total Not issued and not in stock.

the formula in B10 is correct and can be copied across for c10 Etc

its the formula in B11 im having trouble with, this should total 4 using the information in the attached i just cant get it to add the 2 columns.

i have also just found out that the formula in B10 may not work as i need the dates for each month and the formula i have will add anything after that

 

[Fluff]

You need to use the formula I supplied for row 10.
And for row 11 you can use

=SUMPRODUCT(('FG Warehouse Perpetual Tracker'!$K$4:$K$100>=B$1)*('FG Warehouse Perpetual Tracker'!$K$4:$K$100<=EOMONTH(B$1,0))*('FG Warehouse Perpetual Tracker'!$M$4:$N$100))

 

[davidmor]

Hi Fluff,

That's Perfect sorry if my messages were confusing, i knew what i wanted to say but wasn't sure i was saying correctly

 

[Fluff]

Glad to help & thanks for the feedback.