Countif on a lookup range

[shadow139]

Hello,

I need help on how to to count a specific value on a range after a lookup. Basically, I want to count the number of values with a condition which is date (Weekend date). Is there a way to lookup the range instead of manually inputting every time I need to count a specific value in range of dates? Also is it possible to make the formula dynamic? I need to calculate data for years.

Example: If WE 8.5 appears on any column, count how many absences under those columns.

	WE 8.5	WE 8.5	WE 8.5	WE 8.5	WE 8.5	WE 8.12	WE 8.12	WE 8.12	WE 8.12	WE 8.12
	1-Aug	2-Aug	3-Aug	4-Aug	5-Aug	6-Aug	7-Aug	8-Aug	9-Aug	10-Aug
Person 1	Present	Absent	Present	Present	Present	Present	ABSENT	RD	RD	Absent
Person 2	Present	Absent	Present	RD	Present	Present	RD	Present	Absent	Absent
Person 3	Present	Absent	Present	RD	Present	Present	RD	Present	Absent	Absent
Person 4	RD	Absent	Present	Present	RD	Present	ABSENT	Present	Absent	Absent
Person 5	RD	Absent	Present	Present	RD	RDS	ABSENT	Present	Absent	Absent

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

Output Table here:

	Absent	Present	Absent	RD
WE 8.5	9
WE 8.12
WE 8.19
WE 8.26

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

Thanks in advance.

 

[Weazel]

I'm not sure where you're getting 9 from but....

B12=SUMPRODUCT(($B$1:$K$1=$A12)*($B$3:$K$7=B$11))

​

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​		WE 8.5	WE 8.5	WE 8.5	WE 8.5	WE 8.5	WE 8.12	WE 8.12	WE 8.12	WE 8.12	WE 8.12
2​		1-Aug​	2-Aug​	3-Aug​	4-Aug​	5-Aug​	6-Aug​	7-Aug​	8-Aug​	9-Aug​	10-Aug​
3​	Person 1	Present	Absent	Present	Present	Present	Present	ABSENT	RD	RD	Absent
4​	Person 2	Present	Absent	Present	RD	Present	Present	RD	Present	Absent	Absent
5​	Person 3	Present	Absent	Present	RD	Present	Present	RD	Present	Absent	Absent
6​	Person 4	RD	Absent	Present	Present	RD	Present	ABSENT	Present	Absent	Absent
7​	Person 5	RD	Absent	Present	Present	RD	RDS	ABSENT	Present	Absent	Absent
8​
9​
10​
11​		Absent	Present	Absent	RD
12​	WE 8.5	5​	14​	5​	6​
13​	WE 8.12	12​	8​	12​	4​
14​	WE 8.19	0​	0​	0​	0​
15​	WE 8.26	0​	0​	0​	0​

<tbody>
</tbody>

 

[shadow139]

Thank you my good Sir Weazel. It works great! I do appreciate it.

 

[shadow139]

One more thing. Actually, two.

Similar table but this time, it contains numbers.

	WE 8.5	WE 8.5	WE 8.5	WE 8.5	WE 8.5	WE 8.5	WE 8.12	WE 8.12	WE 8.12
Person 1	100	65	99	RD	RD	120	100	100	100
Person 2	RD	RD	55	66	44	99	95	20	RD
Person 3	100	100	120	122	RD	RD	52	55	32
Person 4	150	89	RD	RD	90	80	99	88	78

<tbody>
</tbody>

1. I would like to sum all the numbers in all column that contains WE 8.5. I tried this formula =SUMPRODUCT((range1="WE 8.5")*(range2>0)) but it only returned 294 which is incorrect.

2. I would like to count how many cells that contains numbers with the same argument above.

 

[CatyH]

THANK YOU SOOOOOOO very much for posting this, Weazel! This is the most amazing formula I've run across in a long long time! This may be the best thing since Slicers!

I'm not sure where