SOLVED

How can I simplify my calculation and where am I wrong

New Contributor

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.

Screenshot 2022-08-23 135117.jpg

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.

Screenshot 2022-08-23 135140.jpg

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 Replies
best response confirmed by Hans Vogelaar (MVP)
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.

last 14 days.JPG 

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