countifs with criteria range by column and in rows (2 rows) one row is by date

[ExcelNewbie2020]

Hello Experts,

I am stuck with this excel problem.

I would like to count how many A, B and C on a table with criteria based on column and in rows. In which, one of the row criteria is range from January 01, 2020 up to December 31, 2020. Please see screen shot. (formula should be place on the yellow highlight).

I really appreciate your help.

Thank you very much.

 

[jasonb75]

Try this in O4, then drag right and down to fill the table.

=IFERROR(1/(1/COUNTIFS($C$3:$L$3,">="&DATEVALUE(LOOKUP("zzz",$O$2:O$2)&2020),$C$3:$L$3,"<="&EOMONTH(DATEVALUE(LOOKUP("zzz",$O$2:O$2)&2020),0),$C4:$L4,O$3)),"")

This assumes N4:N7 will have the same names in the same order as B4:B7, if not an additional INDEX and MATCH combination will need to be added into the formula.

 

[ExcelNewbie2020]

Try this in O4, then drag right and down to fill the table.

=IFERROR(1/(1/COUNTIFS($C$3:$L$3,">="&DATEVALUE(LOOKUP("zzz",$O$2:O$2)&2020),$C$3:$L$3,"<="&EOMONTH(DATEVALUE(LOOKUP("zzz",$O$2:O$2)&2020),0),$C4:$L4,O$3)),"")

This assumes N4:N7 will have the same names in the same order as B4:B7, if not an additional INDEX and MATCH combination will need to be added into the formula.

Sir, the names will not be in order. How, can I insert index and match?..Honestly, I am lost with your given formula.. thanks

 

[jasonb75]

In that case, try

=IFERROR(1/(1/COUNTIFS($C$3:$L$3,">="&DATEVALUE(LOOKUP("zzz",$O$2:O$2)&2020),$C$3:$L$3,"<="&EOMONTH(DATEVALUE(LOOKUP("zzz",$O$2:O$2)&2020),0),INDEX($C:$L,MATCH($N4,$B:$B,0),0),O$3)),"")

If this does not work as expected then you will need to post your example using XL2BB (see link in my signature block below) so that we can use the data to test the formulas.

 

[ExcelNewbie2020]

In that case, try

=IFERROR(1/(1/COUNTIFS($C$3:$L$3,">="&DATEVALUE(LOOKUP("zzz",$O$2:O$2)&2020),$C$3:$L$3,"<="&EOMONTH(DATEVALUE(LOOKUP("zzz",$O$2:O$2)&2020),0),INDEX($C:$L,MATCH($N4,$B:$B,0),0),O$3)),"")

If this does not work as expected then you will need to post your example using XL2BB (see link in my signature block below) so that we can use the data to test the formulas.

It works as expected..Thank you so much sir..