VLOOKUP and COUNTIFS ?

[Dandy83]

Hello everyone,

Here is my problem, I have the following spreadsheet:

	A	B	C			NC
1	Name	01/01/19	01/02/19			12/31/19
2	Franck	F	IC			O
3	Larry	O	O			F
500	Patricia	F	F			IC

<tbody>
</tbody>

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

	A	B	C	D	E	F
1	Name	Start Date	End Date	F	IC	O
2	Louis	01/18/19	02/15/19
3	Sam	06/12/19	06/24/19
500	Larry	05/28/19	07/03/19

<tbody>
</tbody>

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