excel count unique values multiple sheets

[zerbaraa]

Dear All,
I have excel sheet include all year weeks(52) sheet, I count same item from all sheet per month
problem some sheet have two month data..
I tried this formula but it's too long and will take time, to count Educational Activities in unit for January

=COUNTIF('1-Jan'!C3:L20,C7)+COUNTIF('8-Jan'!C3:L20,C7)+COUNTIF('15-Jan'!C3:L20,C7)+COUNTIF('22-Jan'!C3:L20,C7)+COUNTIF('29-Jan'!C3:G20,C7)


please check report sheet in link below
https://drive.google.com/open?id=0B1NBFgB2DavudXY4YzRLZVE4Yk0<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[My Aswer Is This]

Tell me what your trying to count. Maybe I can give you a vba answer.
I do not click on links.

 

[zerbaraa]

(like calendar) each sheet will be Weekly schedule and must select per hour one item from this list (Educational Activities in unit, Educational Activities outside unit, Non-educational Activities, Non-Productive Time, Non-Scheduled Time) and I will count these items per month
<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>

<colgroup><col style="width: 230px"></colgroup><tbody>
</tbody>

 

[Sam_D_Ben]

@ My Answer is this - Hi'

Ref Zerbaraa, Zer tried to count "Educational Activities in unit" for Jan month , but for example on Sheet named "29-Jan", have columns of Feb month datas. So when counting "Educational Activities of Jan month" its counts Feb months also. Means counting should match only for Jan month.

Thanks Muz

 

[Sam_D_Ben]

Hi @ My answer is this, am just showing a sample report of the above case. Please check and help.

Excel 2007
	A	B	C	D	E	F
1	Month	Educational Activities in unit	Educational Activities outside unit	Non-educational Activities	Non-Productive Time	Non-Scheduled Time
2	Jan
3	Feb
4	Mar
5	Apr
6	May
7	Jun
8	Jul
9	Aug
10	Sep
11	Oct
12	Nov
13	Dec
Report

Excel 2007
	A	B	C	D	E	F
1	Date	29-Jan-16	30-Jan-16	31-Jan-16	1-Feb-16	2-Feb-16
2	7:00 AM
3		Educational Activities in unit		Educational Activities in unit
4	8:00 AM
5		Educational Activities in unit
6	9:00 AM
7		Educational Activities outside unit
8	10:00 AM
9
10	11:00 AM
11
29-Jan

Required ...count of each activities Pertaining to each months.


I tried countifs and sumproduct. its going error. I quit




Thanks Muz

 

[My Aswer Is This]

Show me the formula you have now.
You said:

tried to count "Educational Activities in unit

 

[Sam_D_Ben]

Excel 2007
	A	B	C	D
1	1	Month	Educational Activities in unit	Educational Activities outside unit
2	2	Jan	#VALUE!
Report

Cell Formulas
Range	Formula
C2	=COUNTIFS('29-Jan'!C1:G1,Report!B2,'29-Jan'!C1:G11,Report!C1)

Excel 2007
	A	B	C	D	E	F	G
1			Jan	Jan	Jan	Feb	Feb
2	1	Date	29-Jan-16	30-Jan-16	31-Jan-16	1-Feb-16	2-Feb-16
3	2	7:00 AM
4	3		Educational Activities in unit		Educational Activities in unit
29-Jan

What is did is i inserted one row in 29-Jan sheet to extract the month to do countifs with the report sheet.


Formula is Report sheet, C2=


COUNTIFS('29-Jan'!C1:G1,Report!B2,'29-Jan'!C1:G11,Report!C1)


But the same going error. Please help.


Thanks Muz

 

[zerbaraa]

Big thanks for Muzama
that's what I am looking