COUNTIFS with multiple criteria in both columns and rows

[Sekiro1899]

Hello, looking to have a recap table of attendance as follows:
- From the dropdown list you choose between 3 options and i'd like to have a count of sick days, timeoff and Tardiness for each employee for March, April...

What formula would fit in ?

I tried with some Countifs mixed with Index Match but it did not work ...
Thanks in advance for your help

 

[Herakles]

Welcome to Mr Excel

It would be better if your raw data was stored differently . Not only would it be easier to maintain, it would be much easier to
analyse the data. The emphasis in terms of how things are done moves away from the reporting to the area of data collection.
The reporting becomes the easier bit.

Data sets should really be long and thin rather than short and wide.

To start with, I suggest a data structure as in the attached image.

 

[Fluff]

Is this for Excel or for Google Sheets?

 

[Sekiro1899]

Hello,
This is for Google Sheet but i need to translate it to Excel later...
I'm still in need of an answer though. Is it possible to have a formula to have the desired result ?

Thanks in advance

 

[Fluff]

Sheets & Excel are not the same thing, so a formula that works in Sheets may not work in Excel & vice-versa.

 

[Sekiro1899]

I understand but in this case it's pretty much an index/match that might be required so I supposed I could ask here

 

[Fluff]

For excel I would use

=BYROW(FILTER(Sheet1!$B$4:$AF$100,Sheet1!$A$4:$A$100=$A3),LAMBDA(br,SUM(--(br="Time"))))

 

[Sekiro1899]

Hello Thank you !

However i don't understand the Lamda(br,Sum(--(br="Time"))) part

Do you also have a solution for GoogleSheet ?

I know i'm asking a lot but it'll really help I'm trying to setup a HR reporting of absences... for an animal refuge

 

[Fluff]

That part is just counting how many times the word "Time" is found on the row.
I don't know much about Sheets, but this might work

=SUMPRODUCT((Sheet1!$A$4:$A$100=$A3)*(Sheet1!$B$4:$AF$100="Time"))

 

[Sekiro1899]

Amazing i knew i could find an easier way like a SumProduct ! Thank you so much Sir FLUFF