Help :: Need to figure out the count of status except this week

ajyexcel

New member
Joined
Dec 17, 2020
Messages
10
Reaction score
0
Points
0
Excel Version(s)
Office 365
Hi All,
Need an urgent help in solving an excel related requirement
I got a requirement and where i find out the count of Status ( categorized into new/old/newest)

I need to figure out 2 things - one of the requirement is to get the count of status (new and Old) regardless of any condition which I can do by Countif Function

Formula-=SUM(COUNTIF(B2:B24,"new"),COUNTIF(B2:B25,"Old"))

This formula will give the combined sum of Old and New count of Status

Other requirement is to find the same count not included in the current week , which means i need to get the count of the status(new and Old) except this current week.
Is there a way to figure it out using DATE and COUNTIF function ??
Please help !!!




Date Status
2021-01-20 new
2021-01-21 new
2021-01-22 newest
2021-01-23 new
2021-01-24 newest
2021-01-25 new
2021-01-26 old
2021-01-27 new
2021-01-28 newest
2021-01-29 old
2021-01-30 new
2021-01-31 new
2021-02-01 newest
2021-02-02 old
2021-02-03 new
2021-02-04 old
2021-02-05 old
2021-02-06 old
2021-02-07 old
2021-02-08 old
2021-02-09 old
2021-02-10 old
2021-02-11 old
 

Attachments

  • Status ajy.xlsx
    9.4 KB · Views: 11
With the this week date cut off point in cell H4 either:
=COUNTIFS(A2:A24,"<" & H4,B2:B24,"new")+COUNTIFS(A2:A24,"<" & H4,B2:B24,"old")
or:
=SUMPRODUCT(--(A2:A24<H4),(B2:B24="new")+(B2:B24="old"))

How is the current week defined (after what date)? Either put a formula which returns that (using TODAY() I imagine) in cell H4 or include the calculation in the COUNTIF/SUMPRODUCT itself.
 
Back
Top