Dynamic lookups and counts

[Lee8800]

Hi All,

This has me stuck!

I need a formula that will count the number of a certain value ("P") based on a value (the Item) in the same row and between a set of dates.

A version of my data is below.

I will have a second table with each Item, and next to it the count of Ps (or another value). The formula needs to be able to accept an input of the item, and have a variable set of dates so that the parameters "Item 1", "03/07/2018", "05/07/2018" and "P" should return the value of 3 (the number of Ps for Item 1 between 3rd and 5th of July)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 46"]Item[/TD]
[TD="width: 22, align: right"]01/07/2018[/TD]
[TD="width: 22, align: right"]02/07/2018[/TD]
[TD="width: 22, align: right"]03/07/2018[/TD]
[TD="width: 22, align: right"]04/07/2018[/TD]
[TD="width: 22, align: right"]05/07/2018[/TD]
[TD="width: 22, align: right"]06/07/2018[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD][/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD][/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD][/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]P[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be greatly appreciated!!

 

[Marcelo Branco]

Maybe something like this

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

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

B​

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

C​

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

D​

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

E​

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

F​

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

G​

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

H​

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

I​

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

J​

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

K​

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

L​

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

1​

[/td][td]

Item​

[/td][td]

01/07/2018​

[/td][td]

02/07/2018​

[/td][td]

03/07/2018​

[/td][td]

04/07/2018​

[/td][td]

05/07/2018​

[/td][td]

06/07/2018​

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

Item​

[/td][td]

Begin​

[/td][td]

End​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Item 1​

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

P​

[/td][td]

P​

[/td][td]

P​

[/td][td]

P​

[/td][td]

P​

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

Item 1​

[/td][td]

03/07/2018​

[/td][td]

05/07/2018​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Item 2​

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

P​

[/td][td]

P​

[/td][td]

P​

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

P​

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

Item 3​

[/td][td]

03/07/2018​

[/td][td]

05/07/2018​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Item 3​

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

P​

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

P​

[/td][td]

P​

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

Criteria in columns I:K

Formula in L2 copied down
=COUNTIFS($B$1:$G$1,">="&J2,$B$1:$G$1,"<="&K2,INDEX($B$2:$G$4,MATCH(I2,$A$2:$A$4,0),0),"P")

Hope this helps

M.

 

[Lee8800]

Perfect Thanks!!!

Much simpler than I thought, kicking myself now...... I can easily adapt to my additional needs!!!! Thanks again!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.