Count appearance in last weeks

Coppy

New Member
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

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

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

Attachments

• Bildschirmfoto 2020-07-30 um 11.06.08.png
251.6 KB · Views: 13

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Well-known Member
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
ABCDEFGH
11.7.20ApplePearBanana
24.7.20Banana
37.7.20AppleBananaToday=30.7.20
410.7.20PearApplePearBanana
515.7.20Pear687
617.7.20BananaPear
720.7.20BananaPear
823.7.20BananaApplePear
924.7.20BananaApplePear
1027.7.20Apple
1128.7.20ApplePear
Sheet1
Cell Formulas
RangeFormula
G3G3=TODAY()
F5:H5F5=SUMPRODUCT((--(\$A\$1:\$A\$11<=\$G\$3))*((\$B\$1:\$D\$11=F4)))

Coppy

New Member

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!

Well-known Member
Glad you have a solution but I don't have any experience with Synology.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,101
Messages
5,835,389
Members
430,354
Latest member
Novice125

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back