How to display number for month

[Gonney]

Hi Team,

The aim of this document is to display the numbers in the Data sheet in the main display sheet but have it broken down into months. I have this as July and August in the example.

The idea is that in the data tab there will be information added and i have an equation to setup and help filter what month it is into the main display tab.

The main display then have that information displayed by using the month as the knock out criteria. I have tried a few different ways but i cant seem to get it to work.

Any support on this would be much appreciated

 

[etaf]

you could use a SUMIFS() based on the dates

you can change the display of the date in c1 , d1 etc - by formatting to just show month

Book6
	A	B	C	D
1			7/1/22	8/1/22
2	person 1		300	100
3	person 2		72	2
4	person 3		46	12
Sheet1

Cell Formulas
Range	Formula
C2:D4	C2	=SUMIFS(data!$C$2:$C$30,data!$A$2:$A$30,$A2,data!$E$2:$E$30,">="&C$1,data!$E$2:$E$30,"<="&EOMONTH(C$1,0))

Book6
	A	B	C	D	E
1					date
2	person 1		300		7/1/22
3	person 2		72		7/1/22
4	person 3		46		7/7/22
5	person 1		100		8/14/22
6	person 2		2		8/15/22
7	person 3		12		8/16/22
8
data

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[Gonney]

Test for number to show.xlsx
	D
10
Maindisplay

Testing Mini sheet

 

[Gonney]

Hi Thank you for the support. I'm trying to share the base level document here (no privacy info)

Where numbers are to be displayed

Test for number to show.xlsx
	A	B	C	D	E
1	Name	Blank	July total number	August total number
2	Person 1		300	72
3	Person 2		150	600
4	Person 3		270	589
5	Person 4		500	230
6
7			Expected numbers in cells above
8			Numbers to come from Data Tab
9
10
11
Maindisplay

Data Tab - where the information is taken out of.

Test for number to show.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Person	Blank	#		Date	Month
2	Person 1		300		7/07/2022	1/07/2022
3	Person 2		150		7/07/2022	1/07/2022
4	Person 3		270		7/07/2022	1/07/2022
5	Person 4		500		7/07/2022	1/07/2022
6	Person 1		72		7/08/2022	1/08/2022
7	Person 2		600		7/08/2022	1/08/2022
8	Person 3		589		7/08/2022	1/08/2022
9	Person 4		230		7/08/2022	1/08/2022
10
11					^ Date entered in to E of actual date
12					^ F cell equation to get month
13					Would be great to use F Cell for knock out criteria
14
15
16
Data

Cell Formulas
Range	Formula
F2:F9	F2	=DATE(YEAR(Data!E2),MONTH(Data!E2),1)

Is there a chance instead of using C row for dates that we could use the F Cell column to get the month from please?

 

[etaf]

so by changing C1 to a date 1/7/22
and format as month

then you dont need to have / calculate column F in the data sheet

if you do want that , then still a SUMIFS() will still work with the date hardcoded
=SUMIFS(data!$C$2:$C$9,data!$A$2:$A$9,maindisplay!$A2,data!$F$2:$F$9,"1/7/22")
=SUMIFS(data!$C$2:$C$9,data!$A$2:$A$9,maindisplay!$A2,data!$F$2:$F$9,"1/8/22")

or will need some manipulation of text to extract from the title heading

i have added to a share as well as xl2bb

SUMIF-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Name	Blank	July	August					July total number	August total number
2	Person 1		300	72		300	72		300	72
3	Person 2		150	600		150	600		150	600
4	Person 3		270	589		270	589		270	589
5	Person 4		500	230		500	230		500	230
6
7			Expected numbers in cells above
8			Numbers to come from Data Tab
maindisplay

Cell Formulas
Range	Formula
C2:D5	C2	=SUMIFS(data!$C$2:$C$30,data!$A$2:$A$30,$A2,data!$E$2:$E$30,">="&C$1,data!$E$2:$E$30,"<="&EOMONTH(C$1,0))
I2:I5	I2	=SUMIFS(data!$C$2:$C$9,data!$A$2:$A$9,maindisplay!$A2,data!$F$2:$F$9,"1/7/22")
J2:J5	J2	=SUMIFS(data!$C$2:$C$9,data!$A$2:$A$9,maindisplay!$A2,data!$F$2:$F$9,"1/8/22")

SUMIF-ETAF.xlsx

Shared with Dropbox

www.dropbox.com

 

[Gonney]

Etaf you are an absolute legend! Thank you so much it works perfectly now and I've also learned about Sumif at the same time and a few different ways I can look at using it in the future! <3.

 

[etaf]

you are welcome