Forum Discussion

APJKERR's avatar
APJKERR
Copper Contributor
Oct 02, 2022

Counting times a value appears in one column based on the date of another

Hi,

Very much a not an expert here but have a question based on database. Basically, I have a tracker recording building/facilities faults within in place of work e.g. Light fixing X is broken etc etc.

The tracker has a list of the faulty assets in one column, and the date the asset was faulted in another. I'd like to add a further column counting the number of times a particular asset has been faulted in the six months previous to the most recently recorded fault. Is this possible? Would appreciate any advise/steering.

Thanks

excel
Formulas and Functions
training
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 02, 2022

    APJKERR 

    =SUMPRODUCT(($D$2:$D$25<LARGE(IF($C$2:$C$25=F6,$D$2:$D$25),1))*($D$2:$D$25>=LARGE(IF($C$2:$C$25=F6,$D$2:$D$25),1)-180)*($C$2:$C$25=F6))

    You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

     

    The formula returns the number of faults within the last 180 days previous to the most recent fault.

     

Resources