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.
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.
