Sum Values in Table Against Value in 2nd Table

[Grasor]

Good afternoon,

This week IT updated our office suite to 365 but defaulted the Macro security settings from "with notification" to "require digital signature". However, we can't seem to get digital signatures to work and now are macros are all useless. That's a separate matter I'm trying to work out with enterprise IT, I'm hopeful they see the error of their ways and return to the original setting that's worked seemingly fine for at least my past 15 years working on these.

For now, I'm trying to address an issue that would be handily resolved with a custom VBA function.

I have running table of status codes assigned to personnel which describe their status on a particular day. My manager wants to quickly determine which codes are considered "Present" or "Absent" by referencing a 2nd table of values. He wants to be able to adjust which codes are considered "Present" or "Absent" by adjusting the reference in the 2nd table in case policy changes the outcome for each individual code.

What I have done so far is use

=SUM(COUNTIF(Range,{"Code1","Code2","Code3","etc"}))

to get the numbers I should get, but I can't figure out how to change the arrayed portion to a table reference.

Any ideas?

Example.xlsx
	A	B	C	D	E	F	G	H	I	J
4	Names	3/1/2022	3/2/2022	3/3/2022	3/4/2022	3/5/2022			Reference Table
5	Person 1	P	C	C	P	P			P	PRESENT
6	Person 2	E	P	P	P	P			E	PRESENT
7	Person 3	K	M	M	P	P			K	PRESENT
8	Person 4	C	U	U	U	C			C	PRESENT
9									M	ABSENT
10	Count Present	4	2	2	3	4			U	ABSENT
11	Count Absent	0	2	2	1	0
12	Total	4	4	4	4	4
Example Sheet

Cell Formulas
Range	Formula
B10:F10	B10	=SUM(COUNTIF(B5:B8,{"P","E","K","C"}))
B11:F11	B11	=SUM(COUNTIF(B5:B8,{"M","U"}))
B12:F12	B12	=SUM(B10:B11)

 

[Marcelo Branco]

Try

B10 copied across
=SUMPRODUCT(COUNTIFS($I$5:$I$10,B$5:B$8,$J$5:$J$10,"PRESENT"))

B11 copied across
=SUMPRODUCT(COUNTIFS($I$5:$I$10,B$5:B$8,$J$5:$J$10,"ABSENT"))

M.

 

[Peter_SSs]

Another option would be this, copied across and down.

22 03 27.xlsm
	A	B	C	D	E	F	G	H	I	J
4	Names	3/01/2022	3/02/2022	3/03/2022	3/04/2022	3/05/2022			Reference Table
5	Person 1	P	C	C	P	P			P	PRESENT
6	Person 2	E	P	P	P	P			E	PRESENT
7	Person 3	K	M	M	P	P			K	PRESENT
8	Person 4	C	U	U	U	C			C	PRESENT
9									M	ABSENT
10	Count Present	4	2	2	3	4			U	ABSENT
11	Count Absent	0	2	2	1	0
Grasor

Cell Formulas
Range	Formula
B10:F11	B10	=COUNT(FILTER(ROW(B$5:B$8),VLOOKUP(B$5:B$8,$I$5:$J$10,2,0)=MID($A10,7,7),""))

 

[Grasor]

Marcelo & Peter,

Thank you. Both solutions worked but I can only mark one as the answer.

I was thrown an additional request later, which was to also delineate by employee type. To which I was able to adapt Peter's use of the FILTER() function as follows:

=COUNT(FILTER(ROW(C$5:C$8),($B$5:$B$8="E")*(VLOOKUP(C$5:C$8,$J$5:$K$10,2,0)="PRESENT"),""))

Where "E" is the employee type.

Thank you again!

P.S. This situation has proven what I've known for several years: Once you get proficient in VBA it's easy to go straight to that and not use the built-in functions. Now I feel clueless with the built-ins!! Good thing this board exists!

 

[Peter_SSs]

Glad you got a successful outcome. Thanks for letting us know.