COUNTIFS doesn't work properly Correction needed or alternative Function

[Alysiin P]

Greetings Excel Wizard,

I am working on a sheet and I want to use the COUNTIFS function on another to obtain data from another TAB

The criteria I want is Data of a particular name, position and Month to count the number of letter L are in the month.

I tried doing this on the Tab "Suivi congés_RR 2019" but I got a #VALUE! as a solution. Not what I want.

https://drive.google.com/open?id=1POCBS8l9pI-27zO44VOiATvQjbD2SSus

This is a sample of the Excel sheet.

Thanks for your time to review my problem.

You just save my brain from try to guess what I was doing wrong.

 

[DanteAmor]

That way you do not count, explain what you want to do?


'Planning congés_2019'!16:16,"'Planning congés_2019'!'Planning congés_2019'!B35"

 

[Alysiin P]

Thanks for the offer to Help.

I want to count all the cells having the letter L.

 

[DanteAmor]

Try whit this:

=SUMPRODUCT(('Planning congés_2019'!$C$16:$C$40=A4)*('Planning congés_2019'!$D$16:$D$40=B4)*('Planning congés_2019'!$M$16:$NL$40="L"))

 

[Alysiin P]

Greetings Dante Thanks for the formula but the formula you gave calculates the total leave(L) days taken in the year and fails to separate it by the months in tab-cell ='Planning congés_2019'!14 eg for Jan it is ='Planning congés_2019'!L14:AP14 and Cell G3 in the tab Suivi

I will be grateful if you have another option for me.

 

[Alysiin P]

Summarily, In the Tab Suivi Conges I want to have the Total leave(L) days of Mary for the month of january only and for Feb. if there is any cell having the letter L. Not that of the whole year as is the case with the Formula above.

Hope my request is clearer.

Try whit this:

=SUMPRODUCT(('Planning congés_2019'!$C$16:$C$40=A4)*('Planning congés_2019'!$D$16:$D$40=B4)*('Planning congés_2019'!$M$16:$NL$40="L"))

 

[DanteAmor]

Greetings Dante Thanks for the formula but the formula you gave calculates the total leave(L) days taken in the year and fails to separate it by the months in tab-cell ='Planning congés_2019'!14 eg for Jan it is ='Planning congés_2019'!L14:AP14 and Cell G3 in the tab Suivi

I will be grateful if you have another option for me.

Recapitulating:
I:
"explain what you want to do"
You:
"I want to count all the cells having the letter L."

---
So, to understand what you need, you can explain with examples what you have and what you expect as a result.
If you explain it with real examples and with real results it will be easier to help you.

 

[Alysiin P]

I want to have the Total leave(L) days of Mary for the month of January only. if there is any cell having the letter L in row 6.

Recapitulating:
I:
"explain what you want to do"
You:
"I want to count all the cells having the letter L."

---
So, to understand what you need, you can explain with examples what you have and what you expect as a result.
If you explain it with real examples and with real results it will be easier to help you.

 

[RoryA]

Please take a minute to read the forum rules regarding cross-posting and then add links here to your post(s) in other forums. Thanks.

 

[DanteAmor]

Please take a minute to read the forum rules regarding cross-posting and then add links here to your post(s) in other forums. Thanks.

Hi, please add links here to your post(s).