count with horizontal criteria dates and other criteria

[Reggieneo]

Hi All,
how to countifs with horizontal dates range and other criteria,

I have tried sumproducts but just could make it work.
please see sample file for ref
appreciate the help.

 

[Marc L]

Hi !

What is the link with this VBA Macros forum ?​

 

[Reggieneo]

Hi Marc L,
I was hoping to get an excel function or vba solution. No I don’t have the link.


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to the Excel forum

for a formula solution …
​

 

[p45cal]

Formula solution in E17 of the attached. Confirmation in cells N4:N14.

 

[Peter Bartholomew]

SUMPRODUCT will work but the key is to use the sales person to down-select the goods table to a single row. Equally COUNTIFS will work because INDEX returns a reference to the row. Define Name 'selectedGoods' to refer to
= INDEX( goods, MATCH(person,salesPerson,0), )

The answer is given by the worksheet formula
= COUNTIFS( selectedGoods, "<>",
saleDate, ">="&startDate,
saleDate, "<="&endDate )

ps I also agree with p45cal's solution of an INDEX:INDEX pair to define the exact range.

 

[Reggieneo]

Hi p45cal, awesome blank counting with index match, thanks.

Hi Peter, that is one elegant solution. I need to really study to use name range with list formula reference.

thanks to you both, I can use these two.

 

[bosco_yip]

Another formula option

1] In M4, copied down :

=COUNTIF(OFFSET(B4,0,MATCH(C$17,C$2:L$2,0),1,MATCH(D$17,C$2:L$2,0)-MATCH(C$17,C$2:L$2,0)+1),"*")

2] In E17, enter :

=COUNTIF(OFFSET(B$2,MATCH(B17,B$3:B$14,0),MATCH(C$17,C$2:L$2,0),1,MATCH(D$17,C$2:L$2,0)-MATCH(C$17,C$2:L$2,0)+1),"*")

Regards
Bosco