Collecting information

[confusa]

I have 38 sections identical to this (Below) and I am trying to collect all the m's for a particular month in this case November the 1st. Is this possible as Countif(s), Sumif(s), sumproduct, index and match do not work please advise?

	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
September
October
November		m
December																									BH	BH
January	BH																								m
February
March																						m								BH
April		BH
May							BH			m																		BH
June
July
August																											BH

<colgroup><col><col span="26"><col><col span="3"><col></colgroup><tbody>
</tbody>

 

[FDibbins]

I know there is a better way to do this, but cant think of it right now. This also works though...

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Level 1		Level 2					Level 3
2​	Type		Mammal	Bird	Fish	Bug		Dog	Cat	Fresh Water	Marine
3​	Mammal		Dog	Domestic	Fresh Water	Insect		Doberman	Lion	Trout	Shark
4​	Bird		Cat	Wild	Marine	Arachnid		Poodle	Tiger	Minow	Barracuda
5​	Fish		Cow					bulldog	House	Pike
6​	Bugs

A18=COUNTIF(OFFSET($B$3,MATCH($A$17,$A$3:$A$14,0)-1,0,1,38),"m")

 

[confusa]

I know there is a better way to do this, but cant think of it right now. This also works though...

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Level 1		Level 2					Level 3
2​	Type		Mammal	Bird	Fish	Bug		Dog	Cat	Fresh Water	Marine
3​	Mammal		Dog	Domestic	Fresh Water	Insect		Doberman	Lion	Trout	Shark
4​	Bird		Cat	Wild	Marine	Arachnid		Poodle	Tiger	Minow	Barracuda
5​	Fish		Cow					bulldog	House	Pike
6​	Bugs

<tbody>
</tbody>

A18=COUNTIF(OFFSET($B$3,MATCH($A$17,$A$3:$A$14,0)-1,0,1,38),"m")

Morning Ford

Thank you for your reply. This shows as volatile and brings back 0.
Regards
Colin

 

[FDibbins]

My apologies, I uploaded the wrong sample table. The formula does work though...

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

1​

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

13​

14​

15​

16​

17​

2​

September

3​

October

4​

November

m

m

5​

December

6​

January

BH

7​

February

8​

March

9​

April

BH

10​

May

BH

m

11​

June

12​

July

13​

August

14​

15​

16​

17​

November

18​

2​

Enter your selected month in A17
A18=COUNTIF(OFFSET($B$3,MATCH($A$17,$A$3:$A$14,0)-1,0,1,38),"m")
You cold also put the "m" in it's own cell and then reference it.

 

[confusa]

Thank you again for your assistance - I have made this work but it brings back both the November 2nd and 5th entries - I am trying to get it to return one for each day. Is this possible or would I have to find how to do it with VBA?
Regards

Coin

 

[FDibbins]

OK I thought you wanted a count for the whole month (I am trying to collect all the m's for a particular month)

can you show some expected answers please?

 

[confusa]

Not sure if this came through previously - but I am trying to produce a format like the third sheet.

ACL001

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

ACL001

September

September

0

ACL001

October

October

0

ACL001

November

m

November

2

ACL001

December

BH

BH

December

0

ACL001

January

BH

m

January

25

ACL001

February

February

0

ACL001

March

m

BH

March

22

ACL001

April

BH

April

0

ACL001

May

BH

m

BH

May

10

ACL001

June

June

0

ACL001

July

July

0

ACL001

August

BH

August

0

ACL001

AHL001

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

September

m

September

9

AHL001

October

October

0

AHL001

November

m

November

11

AHL001

December

BH

BH

December

0

AHL001

January

BH

January

0

AHL001

February

February

0

AHL001

March

m

BH

March

10

AHL001

April

BH

April

0

AHL001

May

BH

m

BH

May

12

AHL001

June

June

0

AHL001

July

July

0

AHL001

August

BH

August

0

AHL001

0

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

September

VOL001

EPL001

TRL001

BOL001

BAL001

AHL001

IVC001

LGL001

ROL001

CAL001

LLL001

IML001

October

ANL001

SPL001

BOL001

WBL001

UPL001

IVL001

HPL001

SSL001

LGL001

November

TRL001

ACL001

VOL001

HPC001

AHL001

ROL001

BPK001

CAL001

LLL001

December

SPL001

LGL001

ANL001

WBL001

UPL001

TRC001

BAL001

HPL001

January

EPL001

TRL001

VOL001

LLL001

ACL001

SSL001

February

HPC001

SPL001

LGL001

ANL001

BOL001

WBL001

UPL001

PCL001

HPL001

CAL001

ROL001

BPK001

TRC001

IVL001

March

BAL001

AHL001

IVC001

VOL001

ACL001

SSL001

LLL001

IML001

April

ANL001

TRL001

HPC001

SPL001

EPL001

WBL001

UPL001

CAL001

HPL001

May

IVL001

LGL001

ACL001

AHL001

ROL001

BAL001

TRC001

SSL001

June

UPL001

IVC001

BPK001

EPL001

<colgroup><col style="width:48pt" width="64" span="35"> </colgroup><tbody>
</tbody>

 

[FDibbins]

OK now you totally lost me, where do those codes come from, your table shows no reference to them (that I can see)

 

[confusa]

Afternoon Ford

On the far right of each of the 38 blocks (sheet 1 and sheet 2) shows the month day and code, ie January 25 ALC001. The search criteria then looks from last sheet (sheet 3) and references the others thus compiling a list for "m". I would also like to get it to reference others letters (BH) without creating multiples of 38 sheets. Is this possible. I hope I have explained where I would like to be and reduced the confusion.

regards

Colin