Count number of occurences of certain values based on a lookup

[tiawy]

Hi

For each person i want to count the occurences of the letters x and a for alle dates for instance between 1. january 2023 to 5th of january 2023.

The result i want is marked with green in the attached picture.

How can I do this?

Thank you very much

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[tiawy]

I use Office365-version

 

[Fluff]

How about

=SUMPRODUCT((($B$2:$L$10="x")+($B$2:$L$10="a"))*($A$2:$A$10=M5)*($B$1:$L$1>=N5)*($B$1:$L$1<=O5))

 

[tiawy]

Thank you very much... However I cant get the formula to work? Can you also do it with COUNTIFS?

 

[Fluff]

In what way isn't it working?

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		01/01/2023	02/01/2023	03/01/2023	04/01/2023	05/01/2023	06/01/2023	07/01/2023	08/01/2023	09/01/2023	10/01/2023	11/01/2023
2	a	x	d	d
3	b	x	a	d	x
4	c
5	d												b	01/01/2023	01/05/2023	3
6													a	01/01/2023	01/05/2023	1
Data

Cell Formulas
Range	Formula
P5:P6	P5	=SUMPRODUCT((($B$2:$L$10="x")+($B$2:$L$10="a"))*($A$2:$A$10=M5)*($B$1:$L$1>=N5)*($B$1:$L$1<=O5))

Also please update your profile to show your version of Excel.