Counting the first instance

[Iain McBride]

Hi guys,
Trying to calculate the instances of certain occurrences happening.
Count EVERY 1 to 1
Count first instance of Group 1, 2 and 3
Do not count Absent:

I want to calculate it at the bottom of the range B5:B16 (so in cell B17).

can get certain aspects of the formula but not fully working,

Any help gratefully received.

Thanks,
Iain

 

[Peter_SSs]

From the little you have given us it is impossible to know what you have, where and what you are trying to do.

I'd suggest posting a small set of dummy data and the expected results and explain again in relation to that data. My signature block below has suggestions to help with that.

 

[Iain McBride]

[TABLE="width: 416"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Here is a quick snap shot of my table:



[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column1[/TD]
[TD]Date[/TD]
[TD]Date2[/TD]
[TD]Date3[/TD]
[TD]Date4[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]group 1[/TD]
[TD="align: right"]1[/TD]
[TD]1:1[/TD]
[TD]1:1[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Group 1[/TD]
[TD="align: right"]0[/TD]
[TD]Group 1[/TD]
[TD]Group 1[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Group 2[/TD]
[TD="align: right"]1[/TD]
[TD]Group 2[/TD]
[TD]Group 1[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]1:1[/TD]
[TD="align: right"]1[/TD]
[TD]1:1[/TD]
[TD]Group 1[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Abs[/TD]
[TD="align: right"]1[/TD]
[TD]Abs[/TD]
[TD]Group 2[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Abs[/TD]
[TD="align: right"]0[/TD]
[TD]Abs[/TD]
[TD]abs[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Group 1[/TD]
[TD="align: right"]0[/TD]
[TD]Group 1[/TD]
[TD]1:1[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Group 2[/TD]
[TD="align: right"]0[/TD]
[TD]Group 2[/TD]
[TD]1:1[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Group 2[/TD]
[TD="align: right"]0[/TD]
[TD]Group 2[/TD]
[TD]1:1[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Group 1[/TD]
[TD="align: right"]0[/TD]
[TD]group 3[/TD]
[TD]1:1[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Abs [/TD]
[TD="align: right"]1[/TD]
[TD]group 3[/TD]
[TD]1:1[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]1:1[/TD]
[TD="align: right"][/TD]
[TD]1:1[/TD]
[TD]1:1[/TD]
[/TR]
[TR]
[TD]Total Sessions[/TD]
[TD] 4[/TD]
[TD="align: right"][/TD]
[TD]6[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

So the table highlights that every 1:! is counted every first instance of a Group session is counted (the rest are not counted), and the Abs (absent) are not counted.

Hope that clear things (even just a little!)

 

[Peter_SSs]

See if this does what you want.
B14 is copied to D14 & E14

Excel Workbook
	A	B	C	D	E
1	Column1	Date	Date2	Date3	Date4
2	Name	group 1	1	1:1	1:1
3	Name	Group 1	0	Group 1	Group 1
4	Name	Group 2	1	Group 2	Group 1
5	Name	1:1	1	1:1	Group 1
6	Name	Abs	1	Abs	Group 2
7	Name	Abs	0	Abs	abs
8	Name	Group 1	0	Group 1	1:1
9	Name	Group 2	0	Group 2	1:1
10	Name	Group 2	0	Group 2	1:1
11	Name	Group 1	0	group 3	1:1
12	Name	Abs	1	group 3	1:1
13	Name	1:1		1:1	1:1
14	Total Sessions	4		6	9
Count

 

[Iain McBride]

I have now been asked to add an element into the dropdown list (NMN - no meeting needed) that we are not counting (like the 'Abs'). Sadly I do not understand the above formula - can anyone help me to amend the formula.

Thanks
Iain

 

[Iain McBride]

Ignore me - it now auto updates the spreadsheet and the formula works already! Sorryyyyyy

 

[Peter_SSs]

... the formula works already!

That's a relief.