Multiple conditions and min data search

[Bazabelle]

I want to first be sorry for the image, I tried the XL2BB but microsoft blocks it and I cannot use it.

In the image 1 I have added the table I would like to fill and the formula I am searching. The image 2 is the sheet where the raw data is.

Q1 - I want for the country in Sheet 1 to find the min value in column E or G of that country in Sheet 2​

​

Q2 - I want to count for the country in Sheet 1 all the rows that includes a date for that country in Sheet 2 for Column F​

Hope this make sense, thank you in advance

 

[kweaver]

Try this:

Book1 (version 2).xlsb
	A	B	C
2	Canada	12-Jan-23	3
3	France	01-Jan-23	1
4	Japan	09-Sep-22	1
Main

Cell Formulas
Range	Formula
B2:B4	B2	=MIN(FILTER(FILTER('Raw Data'!$E$2:$G$7,A2='Raw Data'!$B$2:$B$7),{1,0,1}))
C2:C4	C2	=COUNTIFS('Raw Data'!$B$2:$B$7,A2,'Raw Data'!$F$2:$F$7,"<>")

 

[DanteAmor]

Try:

In cell B2

=LET(b, 'Raw data'!$B$2:$B$7,a,HSTACK(VSTACK(b,b),VSTACK('Raw data'!$E$2:$E$7,'Raw data'!$G$2:$G$7)),MIN(IF(CHOOSECOLS(a,1)=A2,CHOOSECOLS(a,2))))

In cell D2

=COUNTIFS('Raw data'!$B$2:$B$7,A2,'Raw data'!$F$2:$F$7,B2)

 

[Fluff]

I tried the XL2BB but microsoft blocks it and I cannot use it.

Have a look here XL2BB Icons greyed out

 

[Bazabelle]

=COUNTIFS('Raw data'!$B$2:$B$7,A2,'Raw data'!$F$2:$F$7,B2)

why B2? I need to search for date format, I tried using the "d-mmm-yy" and it is not working and if I use "<>" it will return data that are not a date but has text in the cell

 

[Bazabelle]

=MIN(FILTER(FILTER('Raw Data'!$E$2:$G$7,A2='Raw Data'!$B$2:$B$7),{1,0,1}))

so in my original document (not the image I gave) the columns are not together, so this would not properly work, I should have said this or gave the example with separated columns. For the 2nd one, it would work but then if I have text other then a date it would count it, why I was searching with date format rather then taking out blanks.

 

[DanteAmor]

if I use "<>" it will ...

I think you confused my answer. In my formula I do not have the symbol <>

It would help if you put the expected results, since the columns in image 1 are empty and I am assuming where you want the result of Q1 and where you want the result of Q2.

 

[kweaver]

How does this differ from what you suggest you want?

Book1 (version 2).xlsb
	A	B	C	D	E	F	G	H
1	ID	Country	Name	Dept	Data1	Data2	Data3	Data4
2	123	France			01-Jan-23	09-Jan-23	03-Mar-23	15-Mar-23
3	456	France			14-Apr-23		23-Apr-23	23-Apr-23
4	789	Canada			06-May-23	12-Apr-23	01-Apr-23
5	987	Canada			12-Jan-23	31-Jan-23	18-Jan-23	18-Jan-23
6	654	Japan			15-Sep-22	15-Sep-22	09-Sep-22	22-Sep-22
7	321	Canada			15-Mar-23	31-Mar-23	31-Mar-23
Raw Data

Book1 (version 2).xlsb
	A	B	C
1	Country	Q1	Q3
2	Canada	12-Jan-23	3
3	France	01-Jan-23	1
4	Japan	09-Sep-22	1
Main

Cell Formulas
Range	Formula
B2:B4	B2	=MIN(FILTER(FILTER('Raw Data'!$E$2:$G$7,A2='Raw Data'!$B$2:$B$7),{1,0,1}))
C2:C4	C2	=COUNTIFS('Raw Data'!$B$2:$B$7,A2,'Raw Data'!$F$2:$F$7,"<>")

 

[Bazabelle]

Have a look here XL2BB Icons greyed out

Great Thank you so much, seems to work now..

 

[Bazabelle]

How does this differ from what you suggest you want?

Book1 (version 2).xlsb
	A	B	C	D	E	F	G	H
1	ID	Country	Name	Dept	Data1	Data2	Data3	Data4
2	123	France			01-Jan-23	09-Jan-23	03-Mar-23	15-Mar-23
3	456	France			14-Apr-23		23-Apr-23	23-Apr-23
4	789	Canada			06-May-23	12-Apr-23	01-Apr-23
5	987	Canada			12-Jan-23	31-Jan-23	18-Jan-23	18-Jan-23
6	654	Japan			15-Sep-22	15-Sep-22	09-Sep-22	22-Sep-22
7	321	Canada			15-Mar-23	31-Mar-23	31-Mar-23
Raw Data

Book1 (version 2).xlsb
	A	B	C
1	Country	Q1	Q3
2	Canada	12-Jan-23	3
3	France	01-Jan-23	1
4	Japan	09-Sep-22	1
Main

Cell Formulas
Range	Formula
B2:B4	B2	=MIN(FILTER(FILTER('Raw Data'!$E$2:$G$7,A2='Raw Data'!$B$2:$B$7),{1,0,1}))
C2:C4	C2	=COUNTIFS('Raw Data'!$B$2:$B$7,A2,'Raw Data'!$F$2:$F$7,"<>")

So I think I will have to give a more concrete example later today, the document I have for work I cannot send to my personal email. but essentially I have a couple of columns in between the 2 data I want to return the min and in those columns I will also need to gather min of it on a separate cell, when I tried your formula on that work sheet and not the simple version I gave there is #value error.