How can I simplify my calculation and where am I wrong

[Gunnar_Grieser]

Hello,

I'm new in this community and I do something wrong in my calculation formula.

Hereafter you'll see an example of my problem.

There are multiple cities in the top line.

Each location (Anlage) will be observed for correct function. (second line)

In the original file (confidential) are two more lines with detailed information to the locations.

Everytime something fails, the day and location are marked with a x.

Now I want to analyse the progress during the past 14 days, since we have now a countermeasure for the problems.

What formula do I need to run this analysis every day automatically?

Thx in advance.

Gunnar

[#2 · Accepted solution]

@Gunnar_Grieser 

=SUMPRODUCT(($C$1:$I$1=B30)*IF($C$3:$I$28="x",1)*($A$3:$A$28>=TODAY()-14)*($A$3:$A$28<=TODAY()))

You can try this formula which counts the number of "x" for each city during the last 14 days. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

 

[#3]

@OliverScheurich
Thanks a lot!
Now I even understand my mistake.
I thought way too complicated...