Count per Date and Group

[mucah!t]

Hi all,

Is it possible to count the C's based on the criteria given in J4:L4?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
3		alfa	alfa	alfa	bravo	bravo	charlie	charlie		GROUP	FROM	TILL
4	1-Jan	DF	DF	DF	GH	GH	DN	DN		ALFA	5-Jan	11-Jan
5	2-Jan	DF	DF	DF	C	GH	DN	DN
6	3-Jan	DF	DF	DF						RESULT =	4
7	4-Jan
8	5-Jan	DN	DN	DN	GH	GH	GH	GH
9	6-Jan	DN	C	DN	DN	C	GH	GH
10	7-Jan	DN	DN	DN	DN	DN
11	8-Jan	C	DN	C	DN	DN
12	9-Jan				DN	DN	GH	GH
13	10-Jan				DN	DN	DN	C
14	11-Jan	GH	GH	C			DF	DF
15	12-Jan	GH	GH	GH	DG	DG	DF	DF
16	13-Jan				DG	DG	DF	DF
17	14-Jan				C	DF
18	15-Jan	GH	GH	GH	DF	DF	DN	DN
19	16-Jan	DN	DN	DN	DF	DF	DN	DN
20	17-Jan	DN	DN	DN			DN	DN
21	18-Jan	DN	DN	DN	DN	DN	C	DN
22	19-Jan	DN	DN	DN	DN	C
23	20-Jan	DN	DN	DN	DN	DN
24	21-Jan				DN	DN	GH	GH
25	22-Jan	DG	DG	DG			GH	GH
26	23-Jan	DG	DG	DG
Sheet1

Excel 2003Worksheet
</TR></TBODY>

 

[mucah!t]

A stand-alone C should be 8

A stand-alone C ---> 8

Something like C2 ---> 2

Something like CD ---> 0

 

[Aladin Akyurek]

A stand-alone C should be 8

A stand-alone C ---> 8

Something like C2 ---> 2

Something like CD ---> 0

Right...

Control+shift+enter, not just enter:

=SUM(IF(($A$4:$A$26>=K4)*($A$4:$A$26<=L4)*($B$3:$H$3=J4),
    IF($B$4:$H$26="C",8,IF(ISNUMBER(SUBSTITUTE($B$4:$H$26,"C",0)+0),
      SUBSTITUTE($B$4:$H$26,"C",0)+0))))

Hope it does not exceed the limit of the nesting levels.

 

[mucah!t]

Works like a charm, thanks for your help Aladin!

 

[Aladin Akyurek]

Works like a charm, thanks for your help Aladin!

Graag gedaan...

 

[mucah!t]

One last question

Because this formula takes about 15 seconds on my computer to calculate I was thinking of putting it in a macro.

I tried to copy the formula, while recording a macro, but it gives an syntax error.

Is there a way to solve this?

 

[mucah!t]

I guess I need to open a new thread for this.
So I will