Return the number of unique values in a date range that contain a key sentence

[HatchetHarry]

Moderator edit: Google Sheets question - see post #6

Let's say I have a line with 3 data: email, date of purchase, location

The formula will check that the customer purchased last month, the location is 'Location 1' and will count only ONCE this customer even though he could have bought multiple times last month.

I have 2 formulas that are working individually but I'm struggling to combine:
The first one is checking the last month entries (COL B) and also checking if the cell contains 'Location 1' sentence (COL C) then returns the total.
=COUNTIFS('Orders'!B:B,">=" & EOMONTH(TODAY(),-2)+1,'Orders'!B:B,"<" & EOMONTH(TODAY(),-1)+1, 'Orders'!C:C,"Location 1")

The second one is checking for unique values using the email address.
=SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6))
*To note that if the cell is empty it will return in error, so bonus point if someone can help me to get the full range (i.e: =SUMPRODUCT(1/COUNTIF(A:A,A:A))

From the screenshot above:
Date: MM/DD/YYYY
Unique customers from LAST month should be 2 at Location 1.

Any idea on how to combine them?
Thanks a lot.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also help if you gave us the sample data in a form that we could copy to test with (eg XL2BB)

 

[shinigamilight]

Try this one

=COUNTA(UNIQUE(DROP(FILTER(A2:C7,--(MONTH(B2:B7)=MONTH(EOMONTH(TODAY(),-1)))*--(C2:C7="Location 1")),,-2)))

 

[Peter_SSs]

Try this one

=COUNTA(UNIQUE(DROP(FILTER(A2:C7,--(MONTH(B2:B7)=MONTH(EOMONTH(TODAY(),-1)))*--(C2:C7="Location 1")),,-2)))

I think you would find that
- if zero rows match the conditions it would still return 1
- provided the other conditions match, it would include a row where the month was the previous month but in any year, not just last month.

@HatchetHarry
Thanks for updating your version details.

Here is another one for you to try.

23 01 19.xlsm
	A	B	C	D	E
1
2	a	22/12/2022	Location 1		2
3	a	12/12/2022	Location 1
4	b	18/01/2023	Location 2
5	c	19/12/2022	Location 1
6	c	19/12/2022	Location 1
7	c	12/01/2023	Location 1
Count

Cell Formulas
Range	Formula
E2	E2	=IFNA(ROWS(UNIQUE(FILTER(A2:A7,(TEXT(B2:B7,"mmyy")=TEXT(EDATE(TODAY(),-1),"mmyy"))*(C2:C7="Location 1"),NA()))),0)

 

[HatchetHarry]

Thanks a lot both of you. I’ll try tomorrow and let you know

 

[HatchetHarry]

Hi again.
My team decided to make it collaborative so I need to change to Google Sheet... (sorry about that).
Upon testing both formula on Google Sheet and taking @Peter_SSs data, it returns 0 and @shinigamilight returns 1

I'm not sure you are familiar with Google Sheet.
If you have any idea that's great otherwise thanks a lot for your help.

 

[Peter_SSs]

so I need to change to Google Sheet..

So I have moved your thread to the forum for Google Sheets questions