SOLVED

New Contributor

# How can I simplify my calculation and where am I wrong

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?

Gunnar

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: How can I simplify my calculation and where am I wrong

``=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.