countifs or sumproduct help

[greight]

Hi, I have some data like below.

Month Customer MIKE JANE
1 ABC123 PLT123 ABC123
2 ZSW123 ZSW123 MKL123
3 MKL123
3 PLT123
5 MKL123
6 PLT123
6 ABC123
6 ZSW123
6 ABC123
7 PLT123
8 MKL123
9 MKL123

I have setup two ranges, MIKE (mike's list of customers) and JANE (jane's list of customers). User will select start month and end month. I need the formula to count the number of occurances for the MIKE and JANE defined range between those two dates. I used countifs to count between the two dates where i have a single criteria $A15 (I2 and J2 are start and end month).

=MINUS(countifs(Data16!$V$2:$V$5330,$A15,Data16!$F$2:$F$5330,">="&I$2),countifs(Data16!$V$2:$V$5330,$A15,Data16!$F$2:$F$5330,">"&J$2))

But i can't figure out how to evaluate where $A15 is not a single criteria but a range. I was playing with SUMPRODUCT but have only run into roadblocks. Any assistance would be greatly appreciated.

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	month	customer	mike	jane
2​	1​	abc123	plt123	abc123		month start	month end	mike customer	jane customer
3​	2​	zsw123	zsw123	mkl123		1​	6​	3​	5​
4​	3​	mkl123
5​	3​	plt123
6​	5​	mkl123
7​	6​	abc123
8​	6​	zsw123
9​	6​	abc123
10​	7​	plt123
11​	8​	mkl123
12​	9​	mkl123

<tbody>
</tbody>

h3=SUMPRODUCT((ISNUMBER(MATCH($B$2:$B$12,C$2:C$3,0))*($A$2:$A$12>=$F3)*($A$2:$A$12<=$G3))) copy across

 

[greight]

Works a charm, thank you so much!

 

[MARZIOTULLIO]

Hi

You are welcome