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.

[TABLE="class: grid, width: 715, align: center"]
<colgroup><col><col><col span="9"></colgroup><tbody>[TR]
[TD][/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.12[/TD]
[TD]WE 8.12[/TD]
[TD]WE 8.12[/TD]
[TD]WE 8.12[/TD]
[TD]WE 8.12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1-Aug[/TD]
[TD]2-Aug[/TD]
[TD]3-Aug[/TD]
[TD]4-Aug[/TD]
[TD]5-Aug[/TD]
[TD]6-Aug[/TD]
[TD]7-Aug[/TD]
[TD]8-Aug[/TD]
[TD]9-Aug[/TD]
[TD]10-Aug[/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]ABSENT[/TD]
[TD]RD[/TD]
[TD]RD[/TD]
[TD]Absent[/TD]
[/TR]
[TR]
[TD]Person 2[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]RD[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]RD[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[/TR]
[TR]
[TD]Person 3[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]RD[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]RD[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[/TR]
[TR]
[TD]Person 4[/TD]
[TD]RD[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]RD[/TD]
[TD]Present[/TD]
[TD]ABSENT[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[/TR]
[TR]
[TD]Person 5[/TD]
[TD]RD[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]RD[/TD]
[TD]RDS[/TD]
[TD]ABSENT[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/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]
[/TR]
</tbody>[/TABLE]


Output Table here:

[TABLE="class: grid, width: 331, align: left"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]RD[/TD]
[/TR]
[TR]
[TD]WE 8.5[/TD]
[TD]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WE 8.12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WE 8.19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WE 8.26[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]













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

​[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.12[/TD]
[TD]WE 8.12[/TD]
[TD]WE 8.12[/TD]
[TD]WE 8.12[/TD]
[TD]WE 8.12[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

1-Aug​

[/TD]
[TD]

2-Aug​

[/TD]
[TD]

3-Aug​

[/TD]
[TD]

4-Aug​

[/TD]
[TD]

5-Aug​

[/TD]
[TD]

6-Aug​

[/TD]
[TD]

7-Aug​

[/TD]
[TD]

8-Aug​

[/TD]
[TD]

9-Aug​

[/TD]
[TD]

10-Aug​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Person 1[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]ABSENT[/TD]
[TD]RD[/TD]
[TD]RD[/TD]
[TD]Absent[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Person 2[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]RD[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]RD[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Person 3[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]RD[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]RD[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Person 4[/TD]
[TD]RD[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]RD[/TD]
[TD]Present[/TD]
[TD]ABSENT[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Person 5[/TD]
[TD]RD[/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Present[/TD]
[TD]RD[/TD]
[TD]RDS[/TD]
[TD]ABSENT[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]Absent[/TD]
[/TR]
[TR]
[TD]

8​

[/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]

9​

[/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]

10​

[/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]

11​

[/TD]
[TD][/TD]
[TD]Absent[/TD]
[TD]Present[/TD]
[TD]Absent[/TD]
[TD]RD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]WE 8.5[/TD]
[TD]

5​

[/TD]
[TD]

14​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]WE 8.12[/TD]
[TD]

12​

[/TD]
[TD]

8​

[/TD]
[TD]

12​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]WE 8.19[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]WE 8.26[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

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

 

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

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.5[/TD]
[TD]WE 8.12[/TD]
[TD]WE 8.12[/TD]
[TD]WE 8.12[/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD]100
[/TD]
[TD]65[/TD]
[TD]99[/TD]
[TD]RD[/TD]
[TD]RD[/TD]
[TD]120[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]Person 2[/TD]
[TD]RD[/TD]
[TD]RD[/TD]
[TD]55[/TD]
[TD]66[/TD]
[TD]44[/TD]
[TD]99[/TD]
[TD]95[/TD]
[TD]20[/TD]
[TD]RD[/TD]
[/TR]
[TR]
[TD]Person 3[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]120[/TD]
[TD]122[/TD]
[TD]RD[/TD]
[TD]RD[/TD]
[TD]52[/TD]
[TD]55[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]Person 4[/TD]
[TD]150[/TD]
[TD]89[/TD]
[TD]RD[/TD]
[TD]RD[/TD]
[TD]90[/TD]
[TD]80[/TD]
[TD]99[/TD]
[TD]88[/TD]
[TD]78[/TD]
[/TR]
</tbody>[/TABLE]














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