Aug 23 2022 04:55 AM - edited Aug 23 2022 04:56 AM
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
Aug 23 2022 05:39 AM
Solution=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.
Aug 24 2022 12:13 AM
Aug 23 2022 05:39 AM
Solution=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.