Need help with Excel function

[Avi11]

First post here and a basic user of excel. Go easy on me.

I have a list of parts that were only available on certain dates (marked by 1 as available and 0 as not available in Sheet2). I have a separate table where I want to find out by part if they were available at least Y times in a X number of days.

Here is what I tried and getting #Value !:
=IF(COUNTIFS(Sheet2!$A$4:$A$8,"="&$A4,Sheet2!$B$3:$J$3,">="&B$3,Sheet2!$B$3:$J$3,"<="&B3+($D$1-1))>=$G$1,1,0)

where A is column for parts on both sheets
B3-J3 has dates on both sheets
D1 has the number of days (X)
G1 has the availability (Y)

Any help is appreciated.

 

[thisoldman]

COUNTIFS() requires that all ranges have the same number of rows and columns. A4:A8 is five rows by one column; B3:J3 is one row by nine columns. Would you please post a sample of your data—it's hard for me to reconstruct your worksheets just from the description.

 

[Avi11]

Here are the 2 sheets I described:

Book1
	A	B	C	D	E	F	G	H	I	J
1			X	4		Y	2
2
3		1/15/2018	1/16/2018	1/17/2018	1/18/2018	1/19/2018	1/20/2018	1/21/2018	1/22/2018	1/23/2018
4	ABC1245X	#VALUE!
5	SDHG3K3
6	SJKFH23
7	KJASFH22L
8	LSDNF232
Sheet1

Cell Formulas
Range	Formula
B4	=IF(COUNTIFS(Sheet2!$A$4:$A$8,"="&$A4,Sheet2!$B$3:$J$3,">="&B$3,Sheet2!$B$3:$J$3,"<="&B3+($D$1-1))>=$G$1,1,0)

Book1
	A	B	C	D	E	F	G	H	I	J
3		1/15/2018	1/16/2018	1/17/2018	1/18/2018	1/19/2018	1/20/2018	1/21/2018	1/22/2018	1/23/2018
4	ABC1245X	0	1	0	0	0	0	1	0	1
5	SDHG3K3	0	1	0	0	0	0	0	1	0
6	SJKFH23	0	0	0	0	0	1	0	0	0
7	KJASFH22L	1	0	0	0	1	0	0	0	0
8	LSDNF232	1	0	0	1	0	0	0	0	1
Sheet2

 

[JimboP77]

First post here and a basic user of excel. Go easy on me.

I have a list of parts that were only available on certain dates (marked by 1 as available and 0 as not available in Sheet2). I have a separate table where I want to find out by part if they were available at least Y times in a X number of days.

Here is what I tried and getting #Value !:
=IF(COUNTIFS(Sheet2!$A$4:$A$8,"="&$A4,Sheet2!$B$3:$J$3,">="&B$3,Sheet2!$B$3:$J$3,"<="&B3+($D$1-1))>=$G$1,1,0)

where A is column for parts on both sheets
B3-J3 has dates on both sheets
D1 has the number of days (X)
G1 has the availability (Y)

Any help is appreciated.

So I understand you want to know if the part was available at least 2 times, but I don't understand the number of days part. Do you mean was it available 2 times in a 4 day period?

 

[Avi11]

So I understand you want to know if the part was available at least 2 times, but I don't understand the number of days part. Do you mean was it available 2 times in a 4 day period?

Yes I would like to know if the part was available at least 2 times in a 4 day period.

 

[shg]

I'm sure this could be done with a formula, but it's eluding me at the moment. A UDF is straightforward, though:

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

nTimes​

2​

2​

3​

nDays​

4​

4​

5​

Item \ Day​

1​

2​

3​

4​

5​

6​

7​

8​

9​

​

​

6​

ABC1245X

1​

0​

1​

0​

1​

0​

0​

1​

1​

3​

K6: =MinN(nTimes, nDays, B6:J6)

7​

SDHG3K3

1​

1​

1​

1​

0​

0​

0​

1​

0​

3​

8​

SJKFH23

0​

0​

0​

1​

1​

0​

1​

1​

0​

2​

9​

KJASFH22L

0​

0​

0​

0​

1​

0​

0​

1​

0​

0​

10​

LSDNF232

1​

0​

1​

0​

0​

1​

1​

1​

0​

2​

Function MinN(M As Long, N As Long, r As Range) As Long
  Dim avdInp        As Variant
  Dim nInp          As Long
  Dim i             As Long
  Dim k             As Long

  avdInp = r.Value
  nInp = UBound(avdInp, 2)

  For i = 1 To N
    k = k + avdInp(1, i)
  Next i
  If k >= M Then MinN = MinN + 1

  For i = i To nInp - N + 1
    k = k - avdInp(1, i - N) + avdInp(1, i)
    If k >= M Then MinN = MinN + 1
  Next i
End Function

 

[Avi11]

Bump. Want to see if someone else has any ideas.