Sum between two columns using Offset or SumProduct

[Davesh Garg]

Hi All,
Please assist me on below table so that formula can calculate counts of 'Yes' between 2 "Total" between different months. Please review below table for the reference.

Formula auto calculate Mar month 'Yes' for different persons for that month only. Similarly for Apr and May month. Any help is really appreciated. Thanks

 

[Cubist]

Which version of Excel are you using?

In E2, try:

=COUNTIF(B2:D2,"Yes")

 

[Snakehips]

@Davesh Garg Might this help?

AutoTraining.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Person	3/1/24	3/12/24	3/25/24	Total-Mar	4/2/24	4/8/24	4/15/25	4/22/24	4/29/24	Total-Apr	5/3/24	5/9/24	5/16/24	5/27/24	Total-May
2	P	Yes		Yes	2	Yes		Yes		Yes	3					0
3	S		Yes		1	Yes		Yes			2		Yes			1
4	Q	Yes	Yes	Yes	3				Yes		1					0
5	R		Yes		1						0			Yes		1
6	T				0		Yes			Yes	2	Yes		Yes		2
7	U				0						0					0
Input Template

Cell Formulas
Range	Formula
E2:E7,P2:P7,K2:K7	E2	=SUMPRODUCT(($B2:D2="Yes")*(IFERROR(MONTH($B$1:D$1),99)=MONTH(D$1)))

 

[Snakehips]

On reflection, I think I may be overcomplicating it.
Here is a simpler formula that can be dragged down from E2 and then copy/pasted to K2:K? etc.

AutoTraining.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Person	3/1/24	3/12/24	3/25/24	Total-Mar	4/2/24	4/8/24	4/15/25	4/22/24	4/29/24	Total-Apr	5/3/24	5/9/24	5/16/24	5/27/24	Total-May
2	P	Yes		Yes	2	Yes		Yes		Yes	3					0
3	S		Yes		1	Yes		Yes			2		Yes			1
4	Q	Yes	Yes	Yes	3				Yes		1					0
5	R		Yes		1						0			Yes		1
6	T				0		Yes			Yes	2	Yes		Yes		2
7	U				0						0					0
Input Template

Cell Formulas
Range	Formula
E2:E7,P2:P7,K2:K7	E2	=COUNTIF($B2:D2,"Yes")-SUM($B2:D2)

 

[Davesh Garg]

Which version of Excel are you using?

In E2, try:

=COUNTIF(B2:D2,"Yes")

Thanks for your response and try to assist here. However, this is not what is being required here. Thanks

 

[Davesh Garg]

Thanks Snakehips. This assist and I have added year also in this Sumproduct formula

=SUMPRODUCT(($C2:D2="Yes")*(IFERROR(MONTH($C$1:D$1),99)=MONTH(D$1))*(IFERROR(YEAR($C1:D$1),99)=YEAR(D$1)))

 

[Davesh Garg]

On reflection, I think I may be overcomplicating it.
Here is a simpler formula that can be dragged down from E2 and then copy/pasted to K2:K? etc.

AutoTraining.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Person	3/1/24	3/12/24	3/25/24	Total-Mar	4/2/24	4/8/24	4/15/25	4/22/24	4/29/24	Total-Apr	5/3/24	5/9/24	5/16/24	5/27/24	Total-May
2	P	Yes		Yes	2	Yes		Yes		Yes	3					0
3	S		Yes		1	Yes		Yes			2		Yes			1
4	Q	Yes	Yes	Yes	3				Yes		1					0
5	R		Yes		1						0			Yes		1
6	T				0		Yes			Yes	2	Yes		Yes		2
7	U				0						0					0
Input Template

Cell Formulas
Range	Formula
E2:E7,P2:P7,K2:K7	E2	=COUNTIF($B2:D2,"Yes")-SUM($B2:D2)

Hi Snakehips,
This formula simply helps as compared to SumProduct formula as this one is very simple
Just wanted to confirm if we can apply Offset formula also which auto considers Sum or Count till previous column/row, then it is something which can be used in variable terms even if user added column prior to any Total column, as it is a recurring worksheet where formula has to change it manually otherwise.
Thanks

 

[Davesh Garg]

Hi Snakehips,
This formula simply helps as compared to SumProduct formula as this one is very simple
Just wanted to confirm if we can apply Offset formula also which auto considers Sum or Count till previous column/row, then it is something which can be used in variable terms even if user added column prior to any Total column, as it is a recurring worksheet where formula has to change it manually otherwise.
Thanks

In simple terms, is there a way that D2 can be calculated by some formula considering current cell as Total -1. This way it calculates automatically D2 cell. Please assist on this.
Thanks

 

[Snakehips]

@Davesh Garg I'm sorry but I am not understanding your latest query.