Countif multiple criteria in a table?

[Calv1]

Hi, I have an issue that I've been struggling with and I hope someone could please help me out.

At work we have a standby rota, with one sheet per month. In each sheet there's a rota, and essentially it looks like this (but with each date of the month in it);

	Fri	Sat	Sun	Mon	tue	Wed	Total Days
Employee Name	1	2	3	4	5	6
Person1	B					B	2
Person2		B					1
Person3			B				1
Person4				B			1
Person5					B		1

I've added some index / matches to retrieve the totals of how many days in each month a particular person is on standby, however what I want to do is now get totals of specific dates. So for example how many days person1 has been on standby on a Monday in a given month. I'll then add these together separately for a yearly total.

I can't work out how this would be possible? Some form of index/match/countif array formula ? Arrays are my downfall unfortunately. Any help would be greatly appreciated on this.

 

[Calv1]

I should add, I've tried something like this to no avail;

=COUNTIFS(January!A4:AG11,"B",January!B3:AF3,"Mon",January!A5,"Person1")

 

[Dave Patton]

SumProduct 2020.xlsm
	A	B	C	D	E	F	G	H	I	J	K																						AG	AH
1		Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun																						Total Days	Fri
2	Name	1	2	3	4	5	6	7	8	9	10																						31
3	Person 1	B					B		B																								4	2
4
5b

Cell Formulas
Range	Formula
AG2	AG2	=COUNT(B2:AF2)
AG3	AG3	=COUNTA(B3:AF3)
AH3	AH3	=SUMPRODUCT(--(B3:AF3="B"),--(B1:AF1=AH1))

 

[Calv1]

SumProduct 2020.xlsm
	A	B	C	D	E	F	G	H	I	J	K																						AG	AH
1		Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun																						Total Days	Fri
2	Name	1	2	3	4	5	6	7	8	9	10																						31
3	Person 1	B					B		B																								4	2
4
5b

Cell Formulas
Range	Formula
AG2	AG2	=COUNT(B2:AF2)
AG3	AG3	=COUNTA(B3:AF3)
AH3	AH3	=SUMPRODUCT(--(B3:AF3="B"),--(B1:AF1=AH1))

Dave, thank you very much for the suggestion. This does work in the meantime, however I'd ideally be looking to have this "dynamic" so that I can also add the person name is a parameter.

I'd ideally like to avoid having helper columns on each sheet.

Ultimately I'm adding these totals up on a separate sheet, and being able to sum the whole table at once for "person name", "day" , and "B", would be best as it won't need as much manual intervention with fixed ranges per person.

 

[Habtest]

I should add, I've tried something like this to no avail;

=COUNTIFS(January!A4:AG11,"B",January!B3:AF3,"Mon",January!A5,"Person1")

How about:

=SUMPRODUCT((January!B4:AF11="B")*(January!B3:AF3="Mon")*(January!A4:A11="Person1"))

 

[Dave Patton]

You can expand the SumProduct formula.

The least resource intensive is the example sown above.

Hard code the relevant columns
=SUMPRODUCT((Data!A3:A100=A5)*(Data!B3:B100="B")+(Data!G3:G100="B"))

 

[Calv1]

You can expand the SumProduct formula.

The least resource intensive is the example sown above.

Hard code the relevant columns
=SUMPRODUCT((Data!A3:A100=A5)*(Data!B3:B100="B")+(Data!G3:G100="B"))

How about:

=SUMPRODUCT((January!B4:AF11="B")*(January!B3:AF3="Mon")*(January!A4:A11="Person1"))

Apologies for the delay in getting back to you both on this. But thank you very much for the solutions, they work absolutely perfectly.