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

Med name	nurse name	time	date
			2/1
			2/1
			2/2
			2/3

<tbody>
</tbody>

Medications charted greater than 60 min after removal from machine

Nurse name	medication name	time	Date
			2/1
			2/2

<tbody>
</tbody>

Sheet2 has the table that will total the amount of times a date occurs effectively giving the total amount of occurrences per date
Dates

2/1

2/2

2/3

2/4

2/5

2/6

2/7

2/8

2/9

med not charted

2

1

1

Med charted > 60 min After

1

1

<tbody>
</tbody>

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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell 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)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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

 

[RasGhul]

Thanks for the feedback