Count if formula data from worksheet and table

[Sircotton]

Hello.
I am trying to count the occurrences of dates in a table on a different worksheet and have the information counted and added in another worksheet.
I want to count data in table1 on sheet 1 that matches cell (column heading) in sheet 2. The cell (column heading) in sheet2 is configured to be a "date" 1/1, 1/2, and so on. So every time 1/1 appears in sheet1 table1 it will be counted under the corresponding column in sheet 2 in the table. Whenever Sheet1 table1 has 1/2 it will appear under that column with that heading "1/2". So when sheet1 table has a value that matches the column heading in sheet2 it will add up the occurrences under that column in sheet 2. Does that make sence? Sorry its my first time posting.

 

[RasGhul]

Hi Sircotton,

If you could post some sample data, we could work on a solution for you.

 

[Sircotton]

Sheet1 has the tables inwhich information and dates to be counted are entered
Sheet1 will have a few different tables. shown below are two tables.

Medications not charted[TABLE="width: 500"]
<tbody>[TR]
[TD]Med name[/TD]
[TD]nurse name[/TD]
[TD]time [/TD]
[TD]date [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2/1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2/1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2/2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2/3[/TD]
[/TR]
</tbody>[/TABLE]

Medications charted greater than 60 min after removal from machine
[TABLE="width: 500"]
<tbody>[TR]
[TD]Nurse name[/TD]
[TD]medication name[/TD]
[TD]time [/TD]
[TD]Date [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2/1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2/2[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 has the table that will total the amount of times a date occurs effectively giving the total amount of occurrences per date
Dates
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2/1[/TD]
[TD]2/2[/TD]
[TD]2/3[/TD]
[TD]2/4[/TD]
[TD]2/5[/TD]
[TD]2/6[/TD]
[TD]2/7[/TD]
[TD]2/8[/TD]
[TD]2/9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]med not charted[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Med charted > 60 min After[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So in Table on Sheet 2, Row 1 I want the cell to add up the dates from table1 that match the heading titled with the corresponding date
In row2 I want the cells to add up the dates from table 2 that match the heading titled with the corresponding date.

I am not quite sure how to attach the actual excel document to the post but maybe this will suffice.
I have tried count if and match but i cannot figure out the formula

 

[RasGhul]

Hi Sir,

Maybe this, extend the ranges to suit your data. If these were excel tables we could setup dynamic formulas that would update automatically as you add more rows;

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Medications not charted					Medications charted greater than 60 min after removal from machine
2	Med name	nurse name	time	Date		Nurse name	medication name	time	Date
3				01-02-19					01-02-19
4				01-02-19					02-02-19
5				02-02-19
6				03-02-19
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J
1	Dates	01-02-19	02-02-19	03-02-19	04-02-19	05-02-19	06-02-19	07-02-19	08-02-19	09-02-19
2	Med not charted	2	1	1
3	Med charted > 60 min After	1	1
Sheet2

Cell Formulas
Range	Formula
B2	=COUNTIFS(Sheet1!$D$3:$D$6,">="&Sheet2!B$1,Sheet1!$D$3:$D$6,"<"&(Sheet2!B$1+1))
B3	=COUNTIFS(Sheet1!$I$3:$I$6,">="&Sheet2!B$1,Sheet1!$I$3:$I$6,"<"&(Sheet2!B$1+1))

 

[RasGhul]

Excel Tables method, select your area and CTRL-T to create tables. I named these Medsnotcharted & Medsgreaterthan60;

1 trick is highlight the row(in my example B2-J2) and paste the table formula in B2, then press CTRL+Enter and this will populate the formula corrrectly.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Dates	01-02-19	02-02-19	03-02-19	04-02-19	05-02-19	06-02-19	07-02-19	08-02-19	09-02-19
2	Med not charted	2	1	1
3	Med charted > 60 min After	1	1
Sheet2

Cell Formulas
Range	Formula
B2	=COUNTIFS(Mednotcharted[Date],">="&Sheet2!B$1,Mednotcharted[Date],"<"&(Sheet2!B$1+1))
B3	=COUNTIFS(Medsgreaterthan60[Date],">="&Sheet2!B$1,Medsgreaterthan60[Date],"<"&(Sheet2!B$1+1))

 

[Sircotton]

It works. Thank you so much. This will save me so much time at work performing audits. I really appreciate it.

 

[Sircotton]

Excel Tables method, select your area and CTRL-T to create tables. I named these Medsnotcharted & Medsgreaterthan60;

1 trick is highlight the row(in my example B2-J2) and paste the table formula in B2, then press CTRL+Enter and this will populate the formula corrrectly.

	A	B	C	D	E	F	G	H	I	J
Dates
Med not charted
Med charted > 60 min After

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]01-02-19[/TD]
[TD="align: right"]02-02-19[/TD]
[TD="align: right"]03-02-19[/TD]
[TD="align: right"]04-02-19[/TD]
[TD="align: right"]05-02-19[/TD]
[TD="align: right"]06-02-19[/TD]
[TD="align: right"]07-02-19[/TD]
[TD="align: right"]08-02-19[/TD]
[TD="align: right"]09-02-19[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=COUNTIFS(Mednotcharted[Date],">="&Sheet2!B$1,Mednotcharted[Date],"<"&(Sheet2!B$1+1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=COUNTIFS(Medsgreaterthan60[Date],">="&Sheet2!B$1,Medsgreaterthan60[Date],"<"&(Sheet2!B$1+1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Oh and thank you for posting the "trick". That would have been my next question. haha

 

[RasGhul]

Thanks for the feedback