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:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][td="bgcolor:#C0C0C0"]

J​

[/td][td="bgcolor:#C0C0C0"]

K​

[/td][td="bgcolor:#C0C0C0"]

L​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]

nTimes​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]

nDays​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td="bgcolor:#F3F3F3"]

Item \ Day​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td]

7​

[/td][td]

8​

[/td][td]

9​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]ABC1245X[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td="bgcolor:#CCFFCC"]

3​

[/td][td="bgcolor:#CCFFCC"]K6: =MinN(nTimes, nDays, B6:J6)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]SDHG3K3[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td="bgcolor:#CCFFCC"]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]SJKFH23[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]KJASFH22L[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td="bgcolor:#CCFFCC"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]LSDNF232[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td][/td][/tr]
[/table]

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.