Formula to first find and then sum

[Chris Mcarthur]

I have a workbook with tabs 1 - 31 representing the days of the month.
On each sheet we have employees name, ID, start time, date, and Comments. I currently have a Summary sheet that pulls over the ID and Comment, if any.

What I now need is something to total, if a person (ID) has a comment, how many comments include the word - "sick", how many are "early" and how many do they have that include the word "late".

so for example: ID 542 has 4 early, 2 late, 0 sick ( for the month of February)

 

[RasGhul]

Can you post some sample data so we can workout a solution?

 

[Chris Mcarthur]

How do I add snapshots? attachments, so you can see?

 

[Fluff]

There are add-ins available here to allow you to do that https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Chris Mcarthur]

[TABLE="width: 895"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]February[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]1[/TD]
[TD="colspan: 2"]2[/TD]
[TD="colspan: 2"]3[/TD]
[TD="colspan: 2"]4[/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Comment[/TD]
[TD]ID[/TD]
[TD]Comments[/TD]
[TD]ID[/TD]
[TD]Comments[/TD]
[TD]ID[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]447[/TD]
[TD][/TD]
[TD]299[/TD]
[TD][/TD]
[TD]638[/TD]
[TD]10 mins early[/TD]
[TD]786[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]449[/TD]
[TD][/TD]
[TD]729[/TD]
[TD][/TD]
[TD]450[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]638[/TD]
[TD][/TD]
[TD]300[/TD]
[TD]24 mins late[/TD]
[TD]451[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]450[/TD]
[TD]12 mins early[/TD]
[TD]598[/TD]
[TD][/TD]
[TD]705[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]451[/TD]
[TD][/TD]
[TD]758[/TD]
[TD][/TD]
[TD]536[/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]705[/TD]
[TD][/TD]
[TD]301[/TD]
[TD][/TD]
[TD]452[/TD]
[TD][/TD]
[TD]619[/TD]
[TD]18 mins early'[/TD]
[/TR]
[TR]
[TD]536[/TD]
[TD][/TD]
[TD]742[/TD]
[TD][/TD]
[TD]453[/TD]
[TD][/TD]
[TD]709[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]452[/TD]
[TD][/TD]
[TD]683[/TD]
[TD][/TD]
[TD]861[/TD]
[TD][/TD]
[TD]831[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]453[/TD]
[TD][/TD]
[TD]690[/TD]
[TD][/TD]
[TD]568[/TD]
[TD][/TD]
[TD]832[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]861[/TD]
[TD][/TD]
[TD]305[/TD]
[TD][/TD]
[TD]776[/TD]
[TD]24 mins early[/TD]
[TD]826[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]937[/TD]
[TD][/TD]
[TD]307[/TD]
[TD][/TD]
[TD]463[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]568[/TD]
[TD][/TD]
[TD]820[/TD]
[TD][/TD]
[TD]466[/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]776[/TD]
[TD]45 mins early[/TD]
[TD]309[/TD]
[TD]30 mins early[/TD]
[TD]468[/TD]
[TD][/TD]
[TD]673[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]463[/TD]
[TD][/TD]
[TD]661[/TD]
[TD]45 mins early[/TD]
[TD]865[/TD]
[TD][/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]926[/TD]
[TD]24 mins early[/TD]
[TD]312[/TD]
[TD][/TD]
[TD]899[/TD]
[TD][/TD]
[TD]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]466[/TD]
[TD][/TD]
[TD]701[/TD]
[TD][/TD]
[TD]792[/TD]
[TD][/TD]
[TD]22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]468[/TD]
[TD][/TD]
[TD]316[/TD]
[TD][/TD]
[TD]472[/TD]
[TD][/TD]
[TD]663[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]865[/TD]
[TD][/TD]
[TD]319[/TD]
[TD][/TD]
[TD]880[/TD]
[TD][/TD]
[TD]660[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]899[/TD]
[TD][/TD]
[TD]320[/TD]
[TD]18 mins early[/TD]
[TD]851[/TD]
[TD][/TD]
[TD]662[/TD]
[TD]
Ok this is a sample of the summary tab it picks up the ID# of each employee and any comments from all tabs 1st, 2nd, etc

I will send over a sample of the first tab.

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Chris Mcarthur]

[TABLE="width: 920"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Start Time[/TD]
[TD]Date[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]447[/TD]
[TD]Sidhu Satwinder[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]449[/TD]
[TD]Sidhu Surinder[/TD]
[TD]7:45 Dep[/TD]
[TD]y[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]638[/TD]
[TD]Simmi Simmi[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]450[/TD]
[TD]Singh Baljinder[/TD]
[TD]3:30 PM Fed P[/TD]
[TD]y[/TD]
[TD]12 mins early[/TD]
[/TR]
[TR]
[TD]451[/TD]
[TD]Singh Balvir[/TD]
[TD]2:45 Fed[/TD]
[TD]y[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]705[/TD]
[TD]Singh Daljit[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]536[/TD]
[TD]Singh Deep Pal[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]452[/TD]
[TD]Singh Gurlal[/TD]
[TD]R[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]453[/TD]
[TD]Singh Gurleen[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]861[/TD]
[TD]Singh Gurpratap[/TD]
[TD]<strike>9:30 FIll</strike>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]937[/TD]
[TD]Singh Harmanpreet[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]568[/TD]
[TD]Singh Inderpal[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]776[/TD]
[TD]Singh Ishadeep[/TD]
[TD]330pm F Lead[/TD]
[TD]y[/TD]
[TD]45 mins early[/TD]
[/TR]
[TR]
[TD]463[/TD]
[TD]Singh Paramjit[/TD]
[TD]7:45 Line[/TD]
[TD]y[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]926[/TD]
[TD]Singh Satbir[/TD]
[TD]3:30pm Fed P[/TD]
[TD]y[/TD]
[TD]24 mins early[/TD]
[/TR]
[TR]
[TD]466[/TD]
[TD]Singh Vir[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]468[/TD]
[TD]Skuratow Tamara[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]865[/TD]
[TD]Sran Sukhdeep[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]899[/TD]
[TD]Suyo Conrado[/TD]
[TD]8:00 am Cheese[/TD]
[TD]y[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]792[/TD]
[TD]Takhar Kulwinder[/TD]
[TD]2:30 Bake[/TD]
[TD]y[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]472[/TD]
[TD]Takhar Sukhjit[/TD]
[TD]-[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]880[/TD]
[TD]Tariq Durr E Sameen[/TD]
[TD]8:00 Pack[/TD]
[TD]y[/TD]
[TD]12 mins early[/TD]
[/TR]
[TR]
[TD]851[/TD]
[TD]Tatla Rupinder[/TD]
[TD]6:15 Oven[/TD]
[TD]y[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

Hi Chris,

Maybe this could help? Use a helper table to find & convert full comments into Early/Late or Sick this has to be repeated for all the other tabs.

Then we can add all of the countifs together,

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Name	Start Time	Date	Comment	Early	Late	Sick	Early	Late	Sick
2	447	Sidhu Satwinder
3	449	Sidhu Surinder	7:45 Dep	y
4	638	Simmi Simmi							1
5	450	Singh Baljinder	3:30 PM Fed P	y	12 mins early	Early			2
6	451	Singh Balvir	2:45 Fed	y
7	705	Singh Daljit
8	536	Singh Deep Pal
9	452	Singh Gurlal	R
10	453	Singh Gurleen
11	861	Singh Gurpratap	9:30 FIll
12	937	Singh Harmanpreet
13	568	Singh Inderpal
14	776	Singh Ishadeep	330pm F Lead	y	45 mins early	Early			3
15	463	Singh Paramjit	7:45 Line	y
16	926	Singh Satbir	3:30pm Fed P	y	24 mins early	Early			2
17	466	Singh Vir
18	468	Skuratow Tamara
19	865	Sran Sukhdeep
20	899	Suyo Conrado	8:00 am Cheese	y
21	792	Takhar Kulwinder	2:30 Bake	y
22	472	Takhar Sukhjit	-
23	880	Tariq Durr E Sameen	8:00 Pack	y	12 mins early	Early			1
24	851	Tatla Rupinder	6:15 Oven	y
25	300									1
Summary

Cell Formulas
Range	Formula
F2	=IF(ISNUMBER(SEARCH(F$1,$E2)),F$1,"")
I2	=COUNTIFS($A$2:$A$25,Summary!$A2,$F$2:$F$25,Summary!F$1)+COUNTIFS('1'!$A$2:$A$25,Summary!$A2,'1'!C$2:C$25,Summary!F$1)+COUNTIFS('2'!$A$2:$A$25,Summary!$A2,'2'!C$2:C$25,Summary!F$1)+COUNTIFS('3'!$A$2:$A$25,Summary!$A2,'3'!C$2:C$25,Summary!F$1)+COUNTIFS('4'!$A$2:$A$25,Summary!$A2,'4'!C$2:C$25,Summary!F$1)

Book1
	A	B	C	D	E
1	ID	Comment	Early	Late	Sick
2	447
3	449
4	638
5	450	12 mins early	Early
6	451
7	705
8	536
9	452
10	453
11	861
12	937
13	568
14	776	45 mins early	Early
15	463
16	926	24 mins early	Early
17	466
18	468
19	865
20	899
1

Cell Formulas
Range	Formula
C2	=IF(ISNUMBER(SEARCH(C$1,$B2)),C$1,"")

 

[Chris Mcarthur]

hi RasGhul,

so How can I read the whole "countif" formula? its cut off and did you have to type it or is there a way to autofill?

thank-you
Chris

 

[RasGhul]

Hi Chris,

Just zoom out the mrexcel page so you can see the whole formula, note that the formula needs to be expanded to how many sheets you are counting (31?)

 

[mikerickson]

To do one sheet you could use a formula like

=COUNTIFS(A:Z,447,B:AA,"*sick*")