IF formula syntax

[Dhinakaran]

Hi All,

I've a set of data in which i need to identify my customer status if he's transacted back after a given specific date.

For instance, below is the sample customer data with their transacted dates in column B. Now, I need to identify how many customer have transacted back after 31-Jul-2022 basis the last transacted date.

A B C
Name Transacted Date Current Status
Customer 1 13-Jun-21
Customer 2 16-Jun-22
Customer 3 13-Jun-21
Customer 4 5-May-22
Customer 5 10-May-22
Customer 6 5-May-22
Customer 7 10-May-22
Customer 8 15-Aug-22
Customer 9 5-May-22
Customer 10 5-May-22
Customer 11 10-May-22
Customer 12 15-Aug-22
Customer 13 10-May-22
Customer 14 15-Aug-22
Customer 1 5-May-22
Customer 12 2-Jan-23
Customer 3 15-Jan-23
Customer 4 5-May-22
Customer 8 21-Nov-22
Customer 12 2-Dec-22

TIA.

 

[etaf]

not sure i understand the term

customer have transacted back after 31-Jul-2022

can you give some expected results ?

you only have a few customers with more than 1 entry
so are you asking simple
how many customers - UNIQUE() have an entry which is later than 31st july 22

a countifs() may help
or a unique filter

but as i say - i dont know what you are after

you have 3 versions of excel - 365 has a lot of useful functions like unique() etc
so would a solution in 365 version work ok

 

[Dhinakaran]

Thanks ETAF. Yes would need to know how many customers - UNIQUE() have an entry which is later than 31st july 22.

OR

Let me put it this way. If you see, Customer 12, Customer 8, Customer 3, Customer 14 have come back and transacted post 31-Jul-2022.

Ideally in column C (Current status), For the above set of customer the status should be either True or Transacted if they has transacted back after 31-Jul-2022.

Using Office 365 now.

 

[etaf]

i'm assuming you need to flag each entry - not just a list or filter in a cell

does this work for you
=IF(COUNTIFS($A$2:$A$21,A2,$B$2:$B$21,">"&DATEVALUE("31/7/22")),"transacted after 31-july-22","")

will give the text
"transacted after 31-july-22"
on all the customer entries - where at least 1 is after the 31/jul/22

Book25
	A	B	C
1	Name	Date	Current
2	Customer 1	13-Jun-21
3	Customer 2	16-Jun-22
4	Customer 3	13-Jun-21	transacted after 31-july-22
5	Customer 4	5-May-22
6	Customer 5	10-May-22
7	Customer 6	5-May-22
8	Customer 7	10-May-22
9	Customer 8	15-Aug-22	transacted after 31-july-22
10	Customer 9	5-May-22
11	Customer 10	5-May-22
12	Customer 11	10-May-22
13	Customer 12	15-Aug-22	transacted after 31-july-22
14	Customer 13	10-May-22
15	Customer 14	15-Aug-22	transacted after 31-july-22
16	Customer 1	5-May-22
17	Customer 12	2-Jan-23	transacted after 31-july-22
18	Customer 3	15-Jan-23	transacted after 31-july-22
19	Customer 4	5-May-22
20	Customer 8	21-Nov-22	transacted after 31-july-22
21	Customer 12	2-Dec-22	transacted after 31-july-22
Sheet1

Cell Formulas
Range	Formula
C2:C21	C2	=IF(COUNTIFS($A$2:$A$21,A2,$B$2:$B$21,">"&DATEVALUE("31/7/22")),"transacted after 31-july-22","")

or if you need a list
=TEXTJOIN(", ",,UNIQUE(FILTER(A2:A21,B2:B21>DATEVALUE("31/7/22"))))

Book25
	A	B	C	D	E	F	G
1	Name	Date	Current
2	Customer 1	13-Jun-21					Customer 8, Customer 12, Customer 14, Customer 3
3	Customer 2	16-Jun-22
4	Customer 3	13-Jun-21	transacted after 31-july-22
5	Customer 4	5-May-22
6	Customer 5	10-May-22
7	Customer 6	5-May-22
8	Customer 7	10-May-22
9	Customer 8	15-Aug-22	transacted after 31-july-22		x
10	Customer 9	5-May-22					Customer 12, Customer 8, Customer 3, Customer 14
11	Customer 10	5-May-22					31-Jul-2022.
12	Customer 11	10-May-22
13	Customer 12	15-Aug-22	transacted after 31-july-22		x
14	Customer 13	10-May-22
15	Customer 14	15-Aug-22	transacted after 31-july-22		x
16	Customer 1	5-May-22
17	Customer 12	2-Jan-23	transacted after 31-july-22
18	Customer 3	15-Jan-23	transacted after 31-july-22		x
19	Customer 4	5-May-22
20	Customer 8	21-Nov-22	transacted after 31-july-22
21	Customer 12	2-Dec-22	transacted after 31-july-22
Sheet1

Cell Formulas
Range	Formula
G2	G2	=TEXTJOIN(", ",,UNIQUE(FILTER(A2:A21,B2:B21>DATEVALUE("31/7/22"))))
C2:C21	C2	=IF(COUNTIFS($A$2:$A$21,A2,$B$2:$B$21,">"&DATEVALUE("31/7/22")),"transacted after 31-july-22","")