Count appearance in last weeks

[Coppy]

Hi,

I want to create a formula that counts the appearance of a word in a continuous list over the last weeks:

In my example, this would be how often did we have apple during the last 4 weeks. I tried

=COUNTIFS(A1:A11;">="&TODAY()-28;B1:D11;F4)

but it is not working. Can anyone help me please?

 

[Toadstool]

Hi Coppy,

From the semicolon delimiters and jpg name I would guess you are using a German region PC? This will give you the #NAME error because you'd need the local language equivalent such as:
=ZÄHLENWENNS(A1:A11;">="&HEUTE()-28;B1:D11;F4)

...but this will give a #VALUE error as Microsoft documentation for COUNTIFS says "Important: Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other."

Here's my US region PC solution using SUMPRODUCT (which may be SUMMENPRODUKT for you).

Coppy.xlsx
	A	B	C	D	E	F	G	H
1	1.7.20	Apple	Pear	Banana
2	4.7.20	Banana
3	7.7.20	Apple	Banana			Today=	30.7.20
4	10.7.20	Pear				Apple	Pear	Banana
5	15.7.20	Pear				6	8	7
6	17.7.20	Banana	Pear
7	20.7.20	Banana	Pear
8	23.7.20	Banana	Apple	Pear
9	24.7.20	Banana	Apple	Pear
10	27.7.20	Apple
11	28.7.20	Apple	Pear
Sheet1

Cell Formulas
Range	Formula
G3	G3	=TODAY()
F5:H5	F5	=SUMPRODUCT((--($A$1:$A$11<=$G$3))*(($B$1:$D$11=F4)))

 

[Coppy]

Hi Toadstool,

yes, you are correct I'm working on a German Mac.

I got your example working, thank you!

Also, I tried to get this running in Synology Spreadsheet (which is a little like Google Sheets). However, while it works in Goolge sheets itself, Synology seems to have a problem with it:

Do you have any other idea how I could try realizing the challenge?

Thank you!

 

[Toadstool]

Glad you have a solution but I don't have any experience with Synology.