SUMIF function

[shaggy31]

Hi,

I have an inventory list in Excel in which I keep an income/outcome of goods, it looks like this:

Now, I would like to add a new sheet in which I would have a recapitulation of income and outcome of goods per item per month. Something like this (may the numbers in the photo are wrong but you get an idea what I mean):

I suppose i need to use a IF command also but I don't know how to set it when dates are in question.

Best regards,

 

[arthurbr]

Perhaps try a pivot Table and group dates by month?

 

[shaggy31]

Hi!

Pivot table could work. I don't have much experience with it but I have just tried it after your suggestion. The problem is that the data is sorted per day as in the table and I would like to summarize the quantity of the same article from the same month in one value.

 

[awoohaw]

mr excel questions 29.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Inventory Code	Article Name	Qty	Invoice No.	Date				Formula Cross Tab:
2	Code_1	Article_1	-6	xxx	2023-01-05					January	February	March
3	Code_2	Article_2	-2	xxx	2023-01-05				Code_1	-6	-7	0
4	Code_3	Article_3	-6	xxx	2023-01-05				Code_2	-2	0	0
5	Code_4	Article_4	-6	xxx	2023-01-05				Code_3	-6	0	-7
6	Code_5	Article_5	-6	xxx	2023-01-05				Code_4	-6	0	-7
7	Code_6	Article_6	-6	xxx	2023-01-05				Code_5	-6	570	143
8	Code_5	Article_5	570	yyy	2023-02-10				Code_6	-6	0	0
9	Code_1	Article_1	-7	www	2023-02-15				Code_7	0	0	0
10	Code_3	Article_3	-7	zzz	2023-03-02				Code_8	0	0	0
11	Code_4	Article_4	-7	zzz	2023-03-02				Code_9	0	0	0
12	Code_5	Article_5	-7	zzz	2023-03-02				Code_10	0	0	0
13	Code_12	Article_12	-7	@@@	2023-03-18				Code_11	0	0	0
14	Code_5	Article_5	150	§§§	2023-03-25				Code_12	0	0	-7
15
16	PIVOT TABLE:
17
18	Sum of Qty
19	Row Labels	Jan	Feb	Mar
20	Code_1	-6	-7
21	Code_12			-7
22	Code_2	-2
23	Code_3	-6		-7
24	Code_4	-6		-7
25	Code_5	-6	570	143
26	Code_6	-6
27
28
Sheet1

Cell Formulas
Range	Formula
J2	J2	=DATE(2023,1,1)
K2:L2	K2	=EDATE(J2,1)
J3:L14	J3	=SUM((--(J$2=(($E$2:$E$14)-(DAY($E$2:$E$14))+1)))*(--($I3=$A$2:$A$14))*($C$2:$C$14))

 

[awoohaw]

Hi!

Pivot table could work. I don't have much experience with it but I have just tried it after your suggestion. The problem is that the data is sorted per day as in the table and I would like to summarize the quantity of the same article from the same month in one value.

@shaggy31 Some versions of excel pivot tables have the ability to choose the month from the date in the pivot table dialog window. It is a good idea to put your excel version in your profile so it shows up on your profile button.

The two suggestions i give above only us the month of the date. Even in the formula crosstab I am using full dates as the column headers.
And to select for the dates from the data the calculation converts the actual date to the first of each month to compare to the column header.

 

[arthurbr]

Hi!

Pivot table could work. I don't have much experience with it but I have just tried it after your suggestion. The problem is that the data is sorted per day as in the table and I would like to summarize the quantity of the same article from the same month in one value.

A Pivot Table has the option to group days by weeks, months, years, etc... How to Group Dates in Pivot Tables in Excel (by Years, Months, Weeks)

 

[shaggy31]

mr excel questions 29.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Inventory Code	Article Name	Qty	Invoice No.	Date				Formula Cross Tab:
2	Code_1	Article_1	-6	xxx	2023-01-05					January	February	March
3	Code_2	Article_2	-2	xxx	2023-01-05				Code_1	-6	-7	0
4	Code_3	Article_3	-6	xxx	2023-01-05				Code_2	-2	0	0
5	Code_4	Article_4	-6	xxx	2023-01-05				Code_3	-6	0	-7
6	Code_5	Article_5	-6	xxx	2023-01-05				Code_4	-6	0	-7
7	Code_6	Article_6	-6	xxx	2023-01-05				Code_5	-6	570	143
8	Code_5	Article_5	570	yyy	2023-02-10				Code_6	-6	0	0
9	Code_1	Article_1	-7	www	2023-02-15				Code_7	0	0	0
10	Code_3	Article_3	-7	zzz	2023-03-02				Code_8	0	0	0
11	Code_4	Article_4	-7	zzz	2023-03-02				Code_9	0	0	0
12	Code_5	Article_5	-7	zzz	2023-03-02				Code_10	0	0	0
13	Code_12	Article_12	-7	@@@	2023-03-18				Code_11	0	0	0
14	Code_5	Article_5	150	§§§	2023-03-25				Code_12	0	0	-7
15
16	PIVOT TABLE:
17
18	Sum of Qty
19	Row Labels	Jan	Feb	Mar
20	Code_1	-6	-7
21	Code_12			-7
22	Code_2	-2
23	Code_3	-6		-7
24	Code_4	-6		-7
25	Code_5	-6	570	143
26	Code_6	-6
27
28
Sheet1

Cell Formulas
Range	Formula
J2	J2	=DATE(2023,1,1)
K2:L2	K2	=EDATE(J2,1)
J3:L14	J3	=SUM((--(J$2=(($E$2:$E$14)-(DAY($E$2:$E$14))+1)))*(--($I3=$A$2:$A$14))*($C$2:$C$14))

Thanks! This is a little to complicated for me...
I solved it with Pivot table and grouping by month.

 

[awoohaw]

Thanks! This is a little to complicated for me...
I solved it with Pivot table and grouping by month.

I only did the formula crosstab to give you verification that the grouping by months in pivot tables work. I did not expect you to choose that solution (it also has extraneous records which you probably would not want).

I'm happy you found a solution.

Best Wishes!