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]

February
1	2	3	4
ID	Comment	ID	Comments	ID	Comments	ID	Comments
447		299		638	10 mins early	786
449		729		450		5
638		300	24 mins late	451		6
450	12 mins early	598		705		8
451		758		536		9
705		301		452		619	18 mins early'
536		742		453		709
452		683		861		831
453		690		568		832
861		305		776	24 mins early	826
937		307		463		14
568		820		466		15
776	45 mins early	309	30 mins early	468		673
463		661	45 mins early	865		17
926	24 mins early	312		899		19
466		701		792		22
468		316		472		663
865		319		880		660
899		320	18 mins early	851		662	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.

<tbody>
</tbody>

 

[Chris Mcarthur]

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

<colgroup><col span="5"></colgroup><tbody>
</tbody>

 

[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*")