Count between dates with Criteria

[Wimpie]

Good day

I am working on Excel 2010
Trying to count updates between dates and based on names
It is working for 8 of the people but I can not get it to work for one of my staff:Venter, L. (Louis)

I am using the below formula:
=SUMPRODUCT(--(owssvr!$A$2:$A$24981>=K122),--(owssvr!$A$2:$A$24981<=L122),--(owssvr!$B$2:$B$24981=Sheet1!$A$9))

owssvr!A contains the dates in the sheet where the data is
K is the start date L is the end date
owssvr!B has the names of the staff where the data is
Sheet1!A has the staff names with A9 for Venter, L. (Louis)

It worked before for this staff member but for some reason is not picking it up now.
Any help will be appreciated

 

[Aladin Akyurek]

Errand spaces perhaps...

=SUMPRODUCT(--(owssvr!$A$2:$A$24981>=K122),--(owssvr!$A$2:$A$24981<=L122),--(TRIM(owssvr!$B$2:$B$24981)=Sheet1!$A$9))
​

 

[Wimpie]

Hi Aladin
If I add Trim I get #VALUE !
A Value used in this formula is of the wrong data type

 

[Wimpie]

Hi

Let me also mention that the list in Sheet1 was created by copying column B from owssvr and removing duplicates.

I have also tried to use
=SUMPRODUCT(--(owssvr!$A$2:$A$24981>=K122),--(owssvr!$A$2:$A$24981<=L122),--(TRIM(owssvr!$B$2:$B$24981="Venter, L (Louis)*"))) which also gives me #Value !

 

[Aladin Akyurek]

Care to post 5 rows from the B range of

owssvr?​

 

[Wimpie]

How do I post
I wanted to add the spread sheet

 

[Aladin Akyurek]

​

https://www.mrexcel.com/forum/about-...tachments.html

 

[Wimpie]

<tbody> </tbody> Modified Modified By 2018/06/03 19:31 Venter, L. (Louis) <colgroup><col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <col width="170" style="width: 128pt; mso-width-source: userset; mso-width-alt: 6217;"> <tbody> </tbody>
owssvr file
Start Date End Date Number of Updates 01 June 2018 03 June 2018 #VALUE! 04 June 2018 10 June 2018 #VALUE! 11 June 2018 17 June 2018 #VALUE! 18 June 2018 24 June 2018 #VALUE! 25 June 2018 30 June 2018 #VALUE! #VALUE! #VALUE! #VALUE! <colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;" span="2"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"> <tbody> </tbody> Consultant Dashbord
Modified By Anderson, L. (Louette) De Villiers, D. (Danie) Erasmus, A. (Antoinnette) Maleka, I. (Isaac) Oosthuizen, W. (Wimpie) Smith, I. (Ina) van Eeden, B. (Brandon) Venter, L. (Louis) Lock, J. (Johan) <colgroup><col width="167" style="width: 125pt; mso-width-source: userset; mso-width-alt: 6107;"> <tbody> </tbody>
Sheet1
Start Date End Date Number of Updates 01 June 2018 03 June 2018 0 04 June 2018 10 June 2018 6 11 June 2018 17 June 2018 14 18 June 2018 24 June 2018 2 25 June 2018 30 June 2018 0 0 0 0 <colgroup><col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;" span="2"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"> <tbody> </tbody>

<tbody>
</tbody>

All the other Consultant Dashboards look the same and use the same data
It work 100% for everyone but Venter, L (Louis)
As per the above example for Anderson, L. (Louette) also on Consultant dashboard

 

[Aladin Akyurek]

Care to post what the results should be instead of all those #VALUE ! errors?

Make sure what the input is from which the results must come.