How to use count ifs with if condition for multiple sheets?

[Dave Smith]

Hi Experts,

I need one help if any body can help i will be grateful for that.
If I try to explain my data I have multiple sheets which is working as database and now i am creating a dashboard where the specific number of items is need to counted from this based on year month and date & for year month and week i have tried to make the combobox.

I have tried to do this formula using first count if and then if formula but it is little bit more confusing as well as it is tedious as it need to have one specific sheet for the rough data (like to join various name or condition to search etc). I think this can be done in much better way with vba which will be more effective and no need of having any rough sheet to store or to calculate all this stuffs.

I think i have tried to explain my problem & Pl. let me know if there is any need of more data required.
Pl. find images of dashboard and database in attached images.

Pl. help me i need to sort out plz. by 21st feb.

Regards,
Dave

 

[RasGhul]

This is a formula solution only if you can use it

You could create a tab called "Appended", you can either manually copy both database_1 & 2 data into this new table1, convert this into an excel table.

Alternatively if you're confident with Power query you can append your databases together into one table to calculate from.

When entering the countifs it's easier to enter the formula into 1 cell 1st, then highlight the whole range and enter with CTRL + ENTER.

Note that the Month date is 01/01/2021 formatted as "January-21"

Book1
	A	B	C	D
1	Database for item
2	Sr No.	Date	Brand	product name
3	1	1/01/2020	GHT	Spanner(M10)
4	2	2/01/2020	HYT	Spanner(M16)
5	3	3/01/2020	WDT	Spanner(M30)
6	4	4/01/2020	BHJ	Spanner(M65)
7	5	5/01/2020	KMJ	OiIPlug(M30)
8	6	6/01/2020	ALM	Nail(B46)
9	7	7/01/2020	INB	Nut(M15)
10	8	8/01/2020	OCV	Bolt(M96)
11	9	9/01/2020	OLM	Spanner(M10)
12	10	10/01/2020	UBH	Spanner(M16)
13	11	11/01/2020	QNG	Spanner(M30)
14	12	12/01/2020	RVH	Spanner(M65)
15	13	13/01/2020	GHT	OiIPlug(M30)
16	14	14/01/2020	HYT	Nail(B46)
17	15	15/01/2020	WDT	Nut(M15)
database_1

Book1
	A	B	C	D
1	Database for item
2	Sr No.	Date	Brand	product name
3	1	1/01/2021	GHT	Spanner(M10)
4	2	2/01/2021	HYT	Spanner(M16)
5	3	3/01/2021	WDT	Spanner(M30)
6	4	4/01/2021	BHJ	Spanner(M65)
7	5	5/01/2021	KMJ	OiIPlug(M30)
8	6	6/01/2021	ALM	Nail(B46)
9	7	7/01/2021	INB	Nut(M15)
10	8	8/01/2021	OCV	Bolt(M96)
11	9	9/01/2021	OLM	Spanner(M10)
12	10	10/01/2021	UBH	Spanner(M16)
13	11	11/01/2021	QNG	Spanner(M30)
14	12	12/01/2021	RVH	Spanner(M65)
15	13	13/01/2021	GHT	OiIPlug(M30)
16	14	14/01/2021	HYT	Nail(B46)
17	15	15/01/2021	WDT	Nut(M15)
database_2

Book1
	A	B	C	D	E
1	Sr No.	Date	Brand	product name	Weeknumber
2	1	1/01/2020	GHT	Spanner(M10)	1
3	2	2/01/2020	HYT	Spanner(M16)	1
4	3	3/01/2020	WDT	Spanner(M30)	1
5	4	4/01/2020	BHJ	Spanner(M65)	1
6	5	5/01/2020	KMJ	OiIPlug(M30)	2
7	6	6/01/2020	ALM	Nail(B46)	2
8	7	7/01/2020	INB	Nut(M15)	2
9	8	8/01/2020	OCV	Bolt(M96)	2
10	9	9/01/2020	OLM	Spanner(M10)	2
11	10	10/01/2020	UBH	Spanner(M16)	2
12	11	11/01/2020	QNG	Spanner(M30)	2
13	12	12/01/2020	RVH	Spanner(M65)	3
14	13	13/01/2020	GHT	OiIPlug(M30)	3
15	14	14/01/2020	HYT	Nail(B46)	3
16	15	15/01/2020	WDT	Nut(M15)	3
17	1	1/01/2021	GHT	Spanner(M10)	1
18	2	2/01/2021	HYT	Spanner(M16)	1
19	3	3/01/2021	WDT	Spanner(M30)	2
20	4	4/01/2021	BHJ	Spanner(M65)	2
21	5	5/01/2021	KMJ	OiIPlug(M30)	2
22	6	6/01/2021	ALM	Nail(B46)	2
23	7	7/01/2021	INB	Nut(M15)	2
24	8	8/01/2021	OCV	Bolt(M96)	2
25	9	9/01/2021	OLM	Spanner(M10)	2
26	10	10/01/2021	UBH	Spanner(M16)	3
27	11	11/01/2021	QNG	Spanner(M30)	3
28	12	12/01/2021	RVH	Spanner(M65)	3
29	13	13/01/2021	GHT	OiIPlug(M30)	3
30	14	14/01/2021	HYT	Nail(B46)	3
31	15	15/01/2021	WDT	Nut(M15)	3
Appended

Cell Formulas
Range	Formula
E2:E31	E2	=WEEKNUM(B2)-WEEKNUM(EOMONTH(B2,-1)+1,1)+1

Book1
	A	B	C	D	E	F	G	H	I
5
6	YEAR	2021			Month	January-21		week	2
7						1/01/2021
8
9
10
11			Spanner(M16)	Spanner(M16)	Bolt(M96)	Nail(B46)	OiIPlug(M30)	Spanner(M10)	Spanner(M10)
12		Brand	GHT	WDT	OCV	HYT	GHT	GHT	OLM
13		Number of Item/year	0	0	1	1	1	1	1
14		Number of Item/month	0	0	1	1	1	1	1
15		Number of Item/week	0	0	1	0	0	0	1
16
Dashboard

Cell Formulas
Range	Formula
F7	F7	=F6
C13:I13	C13	=COUNTIFS(Table1[Date],">="&DATE(Dashboard!$B$6,1,1),Table1[Date],"<="&DATE(Dashboard!$B$6+1,1,1)-1,Table1[Brand],Dashboard!C$12,Table1[product name],Dashboard!C$11)
C14:I14	C14	=COUNTIFS(Table1[Date],">="&$F$6,Table1[Date],"<="&EOMONTH($F$6,0),Table1[Brand],Dashboard!C$12,Table1[product name],Dashboard!C$11)
C15:I15	C15	=COUNTIFS(Table1[Date],">="&$F$6,Table1[Date],"<="&EOMONTH($F$6,0),Table1[Brand],Dashboard!C$12,Table1[product name],Dashboard!C$11,Table1[Weeknumber],Dashboard!$I$6)

 

[Dave Smith]

Thank you @RasGhul For your wonderful solution but i need less formula one because in future if I or some other will try to solve this formula puzzle it will be difficult at that time to recall.
Instead of that is it possible to club all data in one sheet and use pivot table?
there data addition in database is done twice a day

Regards,
Dave