VLOOKUP and COUNTIFS ?

[Dandy83]

Hello everyone,

Here is my problem, I have the following spreadsheet:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]NC[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]01/01/19[/TD]
[TD]01/02/19[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/31/19[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Franck[/TD]
[TD]F[/TD]
[TD]IC[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Larry[/TD]
[TD]O[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]Patricia[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]IC[/TD]
[/TR]
</tbody>[/TABLE]

The data in it has to be used in another spreadsheet which looks like that:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]F[/TD]
[TD]IC[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Louis[/TD]
[TD]01/18/19[/TD]
[TD]02/15/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sam[/TD]
[TD]06/12/19[/TD]
[TD]06/24/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]Larry[/TD]
[TD]05/28/19[/TD]
[TD]07/03/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to fill for each person the number of "F", "IC", "O",... for a given period.

I tried to use a COUNTIFS function but didn't succeed (it only gives me the number of occurences for a given period, I can't link it with specified people).

I hope to be clear.

Any help ?

Thanks a lot.

 

[eduzs]

Sumifs

 

[Dandy83]

Hello eduzs,

Unfortunately it won't work as the values are not numeric.

 

[Dandy83]

In fact I'm trying to find the correct value given a specific period and person. Which function can help ?
I even thought about doing it another way using a pivot table but it's not practical given the multiple different periods for each person.

 

[Dandy83]

Up.

Nobody can help ?

 

[mrshl9898]

Sounds like you need to add more criteria to the COUNTIFS?

What's the formula you used?

 

[steve the fish]

You could use countifs if you had just one of each name. If not maybe this:

=SUMPRODUCT((Sheet1!$A$2:$A$500=$A2)*(Sheet1!$B$1:$NC$1>=$B2)*(Sheet1!$B$1:$NC$1<=$C2)*(Sheet1!$B$2:$NC$500=D$1))