Count appearance in last weeks

Coppy

New Member
Joined
Aug 21, 2012
Messages
7
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
    Bildschirmfoto 2020-07-30 um 11.06.08.png
    251.6 KB · Views: 13

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,280
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 21, 2012
Messages
7
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:

Bildschirmfoto 2020-07-30 um 15.04.27.png


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

Thank you!
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,280
Office Version
  1. 2016
Platform
  1. Windows
Glad you have a solution but I don't have any experience with Synology.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top